SQL Query multiple columns select from same table

Question

I'm trying to join two tables where table 1 is pulling values from table 2 and storing them in separate columns. To describe the situation:

Table 1
[ID][Name][TBL2_ID_Field1][TBL2_ID_Field2]
 1   XYZ   3               4

Table 2
[ID][Type][Description1][Description2]
 3   AA    TEST          TEST
 4   BB    TEST2         TEST2

I need the table to display something like:

[ID][Name][TBL2_ID_Field1_DESC1][TBL2_ID_Field2_DESC1]
 1   XYZ   TEST                  TEST2

Querying as...

SELECT tbl1.id, tbl1.name, tbl2.description1 as "tbl2_id_field1_desc1", tbl2.description1 as "tbl2_id_field2_desc1"
FROM Table1 tbl1, Table2 tbl2
WHERE tbl1.tbl2_id_field1 = table2.id
AND tbl1.tbl2_id_field2 = table2.id

is obviously not working but I'm not sure what else to try.

Any help is appreciated! Please let me know if I haven't clarified enough.

I'm not getting any results since I'm querying using the same table2.id twice for two different fields (which contain different values) — RizJa
– RizJa, Commented Feb 24, 2015 at 21:03

Thorsten Kettner · Accepted Answer · 2015-02-24 21:02:29Z

2

Join table2 twice:

SELECT 
  tbl1.id, 
  tbl1.name, 
  tbl21.description1 as "tbl2_id_field1_desc1", 
  tbl22.description1 as "tbl2_id_field2_desc1"
FROM Table1 tbl1
JOIN Table2 tbl21 ON tbl21.id = tbl1.tbl2_id_field1
JOIN Table2 tbl22 ON tbl22.id = tbl1.tbl2_id_field2;

answered Feb 24, 2015 at 21:02

Thorsten Kettner

96.6k8 gold badges56 silver badges82 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

RizJa Over a year ago

I was sort of hoping to avoid joining multiple times since the way Table2 is structured is to store values that I would otherwise have to create a few other tables for... but this does query what I need and presents in the way I need it so +1 for that.

Thorsten Kettner Over a year ago

Thanks. As to joining twice: there is no way around this. You want to join two records to each table1 record, so it's two joins.

RizJa Over a year ago

Ah well, fair enough. Thanks for your help!

Collectives™ on Stack Overflow

SQL Query multiple columns select from same table

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related