SQL query problems with multiple tables

Question

Have a problem find a query for my tables

I have 2 tables tab table1 and table2 as follow

table1
id (int) auto_increment
table2_id1(int)
table2_id2(int)
table2_id3(int)
table2_id4(int)

table2

id(int) auto
name (varchar)

that I will do is to get a query to show the name for table2.name insted of the table2_id1 integer.

exampel what I will do and havent a query for

1....test...test...test...test
2....mos...test...mos...mos

my tables as follow

Table1

1   1   1   1   1   
2   2   1   2   2

table2

1   test
2  mos

hope that you can understand what I trying to do, probably some easy way to get the result but I cant find it. Im glad of all help I can get.

Bassam Mehanni · Accepted Answer · 2013-01-30 23:26:27Z

2

You need to join to table2 with an alias, 4 different times, please note that as Mike points out, this is not a very good structure, and you are better off creating a different table to handle this one-to-many relation, instead of adding columns on table 1

 SELECT Table1.id, a.name AS name1, b.name AS name2, c.name as name3, d.name AS name4
 FROM Table1
      JOIN Table2 a on Table1.table2_id1 = a.id 
      JOIN Table2 b on Table1.table2_id2 = b.id 
      JOIN Table2 c on Table1.table2_id3 = c.id 
      JOIN Table2 d on Table1.table2_id4 = d.id

edited Jan 30, 2013 at 23:26

answered Jan 30, 2013 at 23:21

Bassam Mehanni

14.9k2 gold badges35 silver badges41 bronze badges

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

3 Comments

Michael Berkowski Over a year ago

Please explain to the OP what this does and why it is necessary.

Mike Brant Over a year ago

This should work. I would note however that this seems like a bit of on odd schema and might be better suited for normalization, so as to prevent having to join to Table2 repeatedly.

Simmern Over a year ago

Thanks for the help with the query, works perfectly =)) And I have learned a new thing of sql =)). Have a nice weekend

thaJeztah · Accepted Answer · 2013-01-30 23:26:45Z

0

Try this

SELECT id, A.name, B.name 
FROM table1 
  LEFT JOIN table2 AS A ON A.id = table1.table2_id1 
  LEFT JOIN table2 AS B ON B.id = table1.table2_id2;

I just included the first two, but you'll be able to add the rest the same way

answered Jan 30, 2013 at 23:26

thaJeztah

29.3k9 gold badges77 silver badges92 bronze badges

Collectives™ on Stack Overflow

SQL query problems with multiple tables

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related