Joining multiple fields to a single table?

[Ben Willcox]

Hi I am having difficulty writing an SQL query to do what I want:

I have 1 table with 2 columns, 'id' and 'name':

tbl_names:
id name
-- ----
1 Bob
2 Jeff
3 Fred
4 Joe
5 Bill


I then have another table which contains several fields which hold id's
from the above table:

tbl_output:
id person1 person2 person3
-- ------- ------- -------
1 2 3 1
2 5 4 3


I need a query that will return the names for the specified id from
tbl_output.
If I have just one 'person' field in tbl_output I would do it with an
inner join like this:

SELECT name from tbl_names
INNER JOIN tbl_names on tbl_names.id = tbl_output.pers on
WHERE tbl_output.id = ?

but I can't figure it out when theres multiple fields to be joined from
the same table...e.g I want to specify tbl_output.id = 1, and it give me:

person1 person2 person3
------- ------- -------
Jeff Fred Bob


Any ideas?

Cheers,
Ben.

[Bill Karwin]

Re: Joining multiple fields to a single table?

Ben Willcox wrote:[color=blue]
> but I can't figure it out when theres multiple fields to be joined from
> the same table...e.g I want to specify tbl_output.id = 1, and it give me:
>
> person1 person2 person3
> ------- ------- -------
> Jeff Fred Bob[/color]

SELECT n1.name as person1, n2.name as person2, n3.name as person3
from tbl_names t inner join tbl_output p on t.id = p.id
inner join tbl_names n1 on p.person1 = n1.id
inner join tbl_names n2 on p.person2 = n2.id
inner join tbl_names n3 on p.person3 = n3.id
where t.id = ?

Supply the value "1" for the "?" parameter.

Regards,
Bill K.

[Ben Willcox]

Re: Joining multiple fields to a single table?

Bill Karwin wrote:
[color=blue][color=green]
>> but I can't figure it out when theres multiple fields to be joined
>> from the same table...e.g I want to specify tbl_output.id = 1, and it
>> give me:
>>
>> person1 person2 person3
>> ------- ------- -------
>> Jeff Fred Bob[/color]
>
>
> SELECT n1.name as person1, n2.name as person2, n3.name as person3
> from tbl_names t inner join tbl_output p on t.id = p.id
> inner join tbl_names n1 on p.person1 = n1.id
> inner join tbl_names n2 on p.person2 = n2.id
> inner join tbl_names n3 on p.person3 = n3.id
> where t.id = ?
>
> Supply the value "1" for the "?" parameter.[/color]

Thanks Bill that worked perfectly in my test database. However when I
tried to modify it for the live app with a slightly different dataset,
it didn't work straight away. I belive this is because the records in my
tbl_names table start at an id of around 700, therefore the first JOIN
cannot link the two id fields together.
I'm still unsure why that first join is required, or how it works, as
there is no relationship between the two id fields? - is it just to
limit the output to 1 record?
Anyway, I managed to get around it by removing the first join, which
cause the query to return lots of identical records (with the data I
want), and adding LIMIT 1 to just give me one of them. Do you think this
is the best way to do it?

Thanks again for your help,

Ben.

[Bill Karwin]

Re: Joining multiple fields to a single table?

Ben Willcox wrote:[color=blue]
> I'm still unsure why that first join is required, or how it works, as
> there is no relationship between the two id fields? - is it just to
> limit the output to 1 record?[/color]

Ah, I was confused about the query you were trying to achieve. Yes,
you're right to take out the first join.
[color=blue]
> Anyway, I managed to get around it by removing the first join, which
> cause the query to return lots of identical records (with the data I
> want), and adding LIMIT 1 to just give me one of them. Do you think this
> is the best way to do it?[/color]

That works, but you can also use "SELECT DISTINCT ..."

Regards,
Bill K.