Mysql query: JOINS

Question

I have the following tables:

Transfers Table:

ID | storagefrom | storageto 
1  | 2           | 3

Storages Table:

ID | Name
2  | Kitchen
3  | Main

I want to get the following result:

Storage1 | Storage2 
Kitchen   | Main

This is my query:

SELECT storages.name as Storage1, storages.name as Storage2
FROM transfers 
LEFT JOIN storages ON storages.id = transfers.storagefrom
LEFT JOIN storages ON storages.id = transfers.storageto

Any tips?

May be this would help you: http://stackoverflow.com/questions/16555454/how-to-generate-auto-increment-field-in-select-query — IsraGab, Jun 22 '16 at 10:19

score 1 · Answer 1 · answered Jun 22 '16 at 10:21

Just use conditional aggregation with CASE EXPRESSION and 1 JOIN :

SELECT t.id,
       MAX(CASE WHEN s.id = t.storagefrom THEN s.name END) as storage_1,
       MAX(CASE WHEN s.id = t.storageto THEN s.name END) as storage_2
FROM transfer t
LEFT JOIN storages s 
 ON(s.id IN(t.storagefrom,t.storageto))
GROUP BY t.id

If it's only 1 record and you don't care about transfer.id than remove it from the select list and drop the GROUP BY clause.

score 1 · Answer 2 · answered Jun 22 '16 at 10:25

1

Just alias your tables

SELECT s1.name as Storage1, s2.name as Storage2
FROM transfers 
LEFT JOIN storages AS s1 ON s1.id = transfers.storagefrom
LEFT JOIN storages AS s2 ON s2.id = transfers.storageto

answered Jun 22 '16 at 10:25

phreakv6

2,135
1
9
11

score 1 · Accepted Answer · answered Jun 22 '16 at 10:32

1

Try

SELECT T.ID,
       S1.Name Storage1,
       S2.name Storage2 
FROM transfers T
LEFT JOIN storages S1 ON S1.ID = T.storagefrom
LEFT JOIN storages S2 ON S2.ID = T.storageto

Refer : SQL Fiddle

answered Jun 22 '16 at 10:32

zakhefron

1,403
1
9
13

Mysql query: JOINS

3 Answers3