CODEWITHSUNDEEP
mysql
Tommizzy
Tommizzy

Reputation: 161

Merging two tables together where all columns don't match

I am trying to put these two tables together:

Orders

id  positionID  Quantity
1   1           1
2   1           1
3   2           2
4   3           1

Positions

id  month   year    position    available   max
1   1       2017    Header      0           2
2   1       2017    Footer      0           2
3   2       2017    Header      1           2
4   2       2017    Footer      2           2

The positionId column in Orders references the id in positions. I know to get those two together I need a JOIN ON statement on the foreign key value, no problem there. But I would also like to include rows directly from the position table. This is where I'm lost. The main purpose of this is to get positions where an order has not been created(like the last row in the example output).

The output I'm looking for is this:

orderID month   year    position    quantity    available   max
1       1       2017    Header      1           0           2
2       1       2017    Header      1           0           2
        1       2017    Header                  0           2
3       2       2017    Footer      2           0           2
        2       2017    Footer                  0           2
4       2       2017    Header      1           1           2
        2       2017    Header                  1           2
        2       2017    Footer                  2           2

Upvotes: 0

Views: 177

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72185

The following query delivers the requested output:

SELECT NULL AS orderId, `month`, `year`, position, NULL AS quantity, 
       available, `max`, id
FROM Positions

UNION ALL

SELECT o.id AS orderId, p.`month`, p.`year`, p.position, o.quantity, 
       p.available, p.`max`, p.id AS id
FROM Orders o 
INNER JOIN Positions p on o.positionId = p.id 
ORDER BY id, CASE WHEN orderId IS NULL THEN 1 ELSE 0 END, position

Upvotes: 1

Related Questions