|
|
Posted by lallous on 02/27/06 02:25
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" <lallous@lgwm.org> wrote in message
news:46eul7FaniqhU1@individual.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" <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]
|