Professional Web Applications Themes

SQL question [2] - MySQL

Hello I have a MySQL query question please. I have two tables: NAMES(id, name) TABLE2(rowid, nameid1, nameid2, nameid3) Example data (NAMES table) ------------------------------- 1 user1 2 user2 3 user3 Example data (TABLE2 table) ------------------------------ 1 1 1 2 2 1 2 3 3 3 2 1 How can I write a query, passing it a 'rowid=1' and it should return something like: rowid=>1, name1=>'user1', name2=>'user1', name3=>'user2' So, when I pass that query a given ROWID it would return (not the ids of the users, but) the names of the users from the NAMES table. Please advise if it is possible ...

  1. #1

    Default SQL question [2]

    Hello

    I have a MySQL query question please.

    I have two tables:

    NAMES(id, name)
    TABLE2(rowid, nameid1, nameid2, nameid3)

    Example data (NAMES table)
    -------------------------------
    1 user1
    2 user2
    3 user3

    Example data (TABLE2 table)
    ------------------------------
    1 1 1 2
    2 1 2 3
    3 3 2 1

    How can I write a query, passing it a 'rowid=1' and it should return
    something like:
    rowid=>1, name1=>'user1', name2=>'user1', name3=>'user2'

    So, when I pass that query a given ROWID it would return (not the ids of the
    users, but) the names of the users from the NAMES table.

    Please advise if it is possible to write that one query, preferrable if it
    is mysql 3.23 compatible.

    --
    Elias


    lallous Guest

  2. #2

    Default Re: SQL question [2]

    "lallous" <lallouslgwm.org> wrote in message
    news:46eo94Fap8opU1individual.net...
    > How can I write a query, passing it a 'rowid=1' and it should return
    > something like:
    > rowid=>1, name1=>'user1', name2=>'user1', name3=>'user2'
    >
    > So, when I pass that query a given ROWID it would return (not the ids of
    > the users, but) the names of the users from the NAMES table.
    Yes, this is fine; it requires three joins to the NAMES table:

    SELECT T.rowid, N1.name, N2.name, N3.name
    FROM TABLE2 AS T
    INNER JOIN NAMES AS N1 ON T.nameid1 = N1.id
    INNER JOIN NAMES AS N2 ON T.nameid2 = N2.id
    INNER JOIN NAMES AS N3 ON T.nameid3 = N3.id

    Regards,
    Bill K.


    Bill Karwin Guest

  3. #3

    Default Re: SQL question [2]

    Bill Karwin wrote:
    > "lallous" <lallouslgwm.org> wrote in message
    > news:46eo94Fap8opU1individual.net...
    >> How can I write a query, passing it a 'rowid=1' and it should return
    >> something like:
    >> rowid=>1, name1=>'user1', name2=>'user1', name3=>'user2'
    >>
    >> So, when I pass that query a given ROWID it would return (not the ids of
    >> the users, but) the names of the users from the NAMES table.
    >
    > Yes, this is fine; it requires three joins to the NAMES table:
    >
    > SELECT T.rowid, N1.name, N2.name, N3.name
    > FROM TABLE2 AS T
    > INNER JOIN NAMES AS N1 ON T.nameid1 = N1.id
    > INNER JOIN NAMES AS N2 ON T.nameid2 = N2.id
    > INNER JOIN NAMES AS N3 ON T.nameid3 = N3.id
    Would be easier to see the result if naming the columns, or else they will be
    listed with the same column name

    SELECT T.rowid, N1.name AS Name1, N2.name AS Name2, N3.name AS Name3
    FROM TABLE2 AS T
    INNER JOIN NAMES AS N1 ON T.nameid1 = N1.id
    INNER JOIN NAMES AS N2 ON T.nameid2 = N2.id
    INNER JOIN NAMES AS N3 ON T.nameid3 = N3.id


    //Aho
    J.O. Aho Guest

  4. #4

    Default Re: SQL question [2]

    Thanks for your answers.

    I am not good with advanced SQL queries. Can you suggest a good link or
    tutorial to get me the essentials stuff regarding the inner joins and
    outer joins, etc...?

    Regards,
    Elias"lallous" <lallouslgwm.org> wrote in message
    news:46eo94Fap8opU1individual.net...
    > Hello
    >
    > I have a MySQL query question please.
    >
    > I have two tables:
    >
    > NAMES(id, name)
    > TABLE2(rowid, nameid1, nameid2, nameid3)
    >
    > Example data (NAMES table)
    > -------------------------------
    > 1 user1
    > 2 user2
    > 3 user3
    >
    > Example data (TABLE2 table)
    > ------------------------------
    > 1 1 1 2
    > 2 1 2 3
    > 3 3 2 1
    >
    > How can I write a query, passing it a 'rowid=1' and it should return
    > something like:
    > rowid=>1, name1=>'user1', name2=>'user1', name3=>'user2'
    >
    > So, when I pass that query a given ROWID it would return (not the ids of
    > the users, but) the names of the users from the NAMES table.
    >
    > Please advise if it is possible to write that one query, preferrable if it
    > is mysql 3.23 compatible.
    >
    > --
    > Elias

    lallous Guest

  5. #5

    Default Re: SQL question [2]

    Hello

    I have two questions:

    1. I need to specify which ROWID to return info for.
    So I added a WHERE close right after the end of the inner joins.

    2. When I executed the query, lots of records were returned instead of one
    expected record (which is the record with the given ROWID).
    So I added a GROUP BY.

    Is what I did correct, or you suggest a better way?

    SELECT T.rowid, N1.name, N2.name, N3.name
    FROM TABLE2 AS T
    INNER JOIN NAMES AS N1 ON T.nameid1 = N1.id
    INNER JOIN NAMES AS N2 ON T.nameid2 = N2.id
    INNER JOIN NAMES AS N3 ON T.nameid3 = N3.id
    WHERE T.rowid = 1
    GROUP BY T.rowid

    --
    Elias
    "J.O. Aho" <userexample.net> wrote in message
    news:46esv4FaulnbU1individual.net...
    > Bill Karwin wrote:
    >> "lallous" <lallouslgwm.org> wrote in message
    >> news:46eo94Fap8opU1individual.net...
    >>> How can I write a query, passing it a 'rowid=1' and it should return
    >>> something like:
    >>> rowid=>1, name1=>'user1', name2=>'user1', name3=>'user2'
    >>>
    >>> So, when I pass that query a given ROWID it would return (not the ids of
    >>> the users, but) the names of the users from the NAMES table.
    >>
    >> Yes, this is fine; it requires three joins to the NAMES table:
    >>
    >> SELECT T.rowid, N1.name, N2.name, N3.name
    >> FROM TABLE2 AS T
    >> INNER JOIN NAMES AS N1 ON T.nameid1 = N1.id
    >> INNER JOIN NAMES AS N2 ON T.nameid2 = N2.id
    >> INNER JOIN NAMES AS N3 ON T.nameid3 = N3.id
    >
    > Would be easier to see the result if naming the columns, or else they will
    > be listed with the same column name
    >
    > SELECT T.rowid, N1.name AS Name1, N2.name AS Name2, N3.name AS Name3
    > FROM TABLE2 AS T
    > INNER JOIN NAMES AS N1 ON T.nameid1 = N1.id
    > INNER JOIN NAMES AS N2 ON T.nameid2 = N2.id
    > INNER JOIN NAMES AS N3 ON T.nameid3 = N3.id
    >
    >
    > //Aho

    lallous Guest

  6. #6

    Default Re: SQL question [2]

    Even my last correction was wrong....

    I think this one works, is it the best though?

    SELECT
    T.rowid, N1.name, N2.name, N3.name
    FROM
    TABLE2 AS T
    INNER JOIN NAMES AS N1 ON T.nameid1 = N1.id
    INNER JOIN NAMES AS N2 ON T.nameid2 = N2.id
    INNER JOIN NAMES AS N3 ON T.nameid3 = N3.id
    WHERE
    T.rowid = 1
    AND
    N1.id = T.nameid1
    AND
    N2.id = T.nameid2
    AND
    N3.id = T.nameid3

    --
    Elias
    "lallous" <lallouslgwm.org> wrote in message
    news:46eul7FaniqhU1individual.net...
    > Hello
    >
    > I have two questions:
    >
    > 1. I need to specify which ROWID to return info for.
    > So I added a WHERE close right after the end of the inner joins.
    >
    > 2. When I executed the query, lots of records were returned instead of one
    > expected record (which is the record with the given ROWID).
    > So I added a GROUP BY.
    >
    > Is what I did correct, or you suggest a better way?
    >
    > SELECT T.rowid, N1.name, N2.name, N3.name
    > FROM TABLE2 AS T
    > INNER JOIN NAMES AS N1 ON T.nameid1 = N1.id
    > INNER JOIN NAMES AS N2 ON T.nameid2 = N2.id
    > INNER JOIN NAMES AS N3 ON T.nameid3 = N3.id
    > WHERE T.rowid = 1
    > GROUP BY T.rowid
    >
    > --
    > Elias
    > "J.O. Aho" <userexample.net> wrote in message
    > news:46esv4FaulnbU1individual.net...
    >> Bill Karwin wrote:
    >>> "lallous" <lallouslgwm.org> wrote in message
    >>> news:46eo94Fap8opU1individual.net...
    >>>> How can I write a query, passing it a 'rowid=1' and it should return
    >>>> something like:
    >>>> rowid=>1, name1=>'user1', name2=>'user1', name3=>'user2'
    >>>>
    >>>> So, when I pass that query a given ROWID it would return (not the ids
    >>>> of the users, but) the names of the users from the NAMES table.
    >>>
    >>> Yes, this is fine; it requires three joins to the NAMES table:
    >>>
    >>> SELECT T.rowid, N1.name, N2.name, N3.name
    >>> FROM TABLE2 AS T
    >>> INNER JOIN NAMES AS N1 ON T.nameid1 = N1.id
    >>> INNER JOIN NAMES AS N2 ON T.nameid2 = N2.id
    >>> INNER JOIN NAMES AS N3 ON T.nameid3 = N3.id
    >>
    >> Would be easier to see the result if naming the columns, or else they
    >> will be listed with the same column name
    >>
    >> SELECT T.rowid, N1.name AS Name1, N2.name AS Name2, N3.name AS Name3
    >> FROM TABLE2 AS T
    >> INNER JOIN NAMES AS N1 ON T.nameid1 = N1.id
    >> INNER JOIN NAMES AS N2 ON T.nameid2 = N2.id
    >> INNER JOIN NAMES AS N3 ON T.nameid3 = N3.id
    >>
    >>
    >> //Aho
    >
    >

    lallous Guest

  7. #7

    Default Re: SQL question [2]

    lallous wrote:
    > Even my last correction was wrong....
    >
    > I think this one works, is it the best though?
    >
    > SELECT
    > T.rowid, N1.name, N2.name, N3.name
    > FROM
    > TABLE2 AS T
    > INNER JOIN NAMES AS N1 ON T.nameid1 = N1.id
    > INNER JOIN NAMES AS N2 ON T.nameid2 = N2.id
    > INNER JOIN NAMES AS N3 ON T.nameid3 = N3.id
    > WHERE
    > T.rowid = 1
    > AND
    > N1.id = T.nameid1
    > AND
    > N2.id = T.nameid2
    > AND
    > N3.id = T.nameid3
    You are doing things twice here,
    the "AND N1.id = T.nameid1" has already been done at "ON T.nameid1 = N1.id".

    And you will get the same column names fro N1.name, N2.name and N3.name when
    you run the query in mysql (all will be listed as name), so AS for each column
    is useful to easier to see the difference between the columns.
    >>> SELECT T.rowid, N1.name AS Name1, N2.name AS Name2, N3.name AS Name3
    >>> FROM TABLE2 AS T
    >>> INNER JOIN NAMES AS N1 ON T.nameid1 = N1.id
    >>> INNER JOIN NAMES AS N2 ON T.nameid2 = N2.id
    >>> INNER JOIN NAMES AS N3 ON T.nameid3 = N3.id
    And just add the WERE statement that is wished for for the query.


    //Aho
    J.O. Aho Guest

  8. #8

    Default Re: SQL question [2]

    lallous wrote:
    > Thanks for your answers.
    >
    > I am not good with advanced SQL queries. Can you suggest a good link or
    > tutorial to get me the essentials stuff regarding the inner joins and
    > outer joins, etc...?
    [url]http://dev.mysql.com/doc/refman/4.1/en/join.html[/url]


    //Aho
    J.O. Aho Guest

  9. #9

    Default Re: SQL question [2]

    lallous wrote:
    > Hello
    >
    > I have two questions:
    >
    > 1. I need to specify which ROWID to return info for.
    > So I added a WHERE close right after the end of the inner joins.
    >
    > 2. When I executed the query, lots of records were returned instead of one
    > expected record (which is the record with the given ROWID).
    > So I added a GROUP BY.
    >
    > Is what I did correct, or you suggest a better way?
    >
    > SELECT T.rowid, N1.name, N2.name, N3.name
    > FROM TABLE2 AS T
    > INNER JOIN NAMES AS N1 ON T.nameid1 = N1.id
    > INNER JOIN NAMES AS N2 ON T.nameid2 = N2.id
    > INNER JOIN NAMES AS N3 ON T.nameid3 = N3.id
    > WHERE T.rowid = 1
    > GROUP BY T.rowid
    "GROUP BY T.rowid" is quite useless as you already have limited the results to
    be "T.rowid = 1", which means you will only have one group only and I guess
    the rowid is a primary key/unique whcih makes you anyhow will only have one
    row in the database with a specific rowid, so grouping will be quite pointless
    for the rowid column.


    //Aho
    J.O. Aho Guest

Similar Threads

  1. Newbie Question: Biz Card Template Question
    By Thomas_Porter@adobeforums.com in forum Adobe Indesign Windows
    Replies: 4
    Last Post: May 30th, 08:08 AM
  2. Replies: 9
    Last Post: April 27th, 04:44 AM
  3. Pen Tool Use Question. (Embarrassingly Newbie Question)
    By Bozo Schmozo in forum Macromedia Flash
    Replies: 0
    Last Post: November 12th, 10:00 PM
  4. Global variable question question
    By Jason Giangrande in forum PHP Development
    Replies: 3
    Last Post: July 23rd, 08:27 PM
  5. newB question: related tables question
    By Blue man in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: June 30th, 04:13 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139