Join question.

midibach · #1 (**permalink**) 02-04-05, 13:18

Hello,

Please excuse my SQL ignorance here.

I have 2 tables that I need to query:

Tablea:

- id
- tableb_id_1
- tableb_id_2

Tableb:

- id
- name

I know that this scenario is probably better handled with 3 tables but I still like to figure it out with the current scheme.

I've used inner joins in the past to pull from both tables, but I'm not exactly sure how to get tablea.id and the tableb.name from both tableb_id_1 AND b_id_2 using 1 query.

Thanks in advance for your help.

r937 · #2 (**permalink**) 02-04-05, 19:29

Code:

select Tablea.id
     , one.name as name1
     , two.name as name2
  from Tablea
left outer
  join Tableb as one
    on Tablea.tableb_id_1 = one.id      
left outer
  join Tableb as two
    on Tablea.tableb_id_2 = two.id

use inner joins if you are positive that both foreign keys are valid

midibach · #3 (**permalink**) 02-05-05, 04:18

Thanks for your help. It totally skipped my mind that I could use AS in the JOIN block.

Internet Services

Web Development

Digital Marketing

Consumer Tech