|
|
Posted by lallous on 02/27/06 02:16
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" <user@example.net> wrote in message
news:46esv4FaulnbU1@individual.net...
> Bill Karwin wrote:
>> "lallous" <lallous@lgwm.org> wrote in message
>> news:46eo94Fap8opU1@individual.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
Navigation:
[Reply to this message]
|