CODEWITHSUNDEEP
mysqlsql
Qvarting
Qvarting

Reputation: 13

sql join - include non existent rows

I have two tables, runtimes where I store runtimes and temps where I store temperatures. I want to fetch temperature for all items on all runtimes. If an item is missed on a runtime I want it to be shown as below.

table runtimes

runtimeId | time
    1     | 2014-04-02 12:00:00
    2     | 2014-04-03 12:00:00

table temps

temps | itemId | runtimeId
 10   |    1   |     1
 20   |    2   |     1
 11   |    1   |     2

Wanted result

runtimeId | time                | temps | itemId 
    1     | 2014-04-02 12:00:00 |   10  |   1
    1     | 2014-04-02 12:00:00 |   20  |   2
    2     | 2014-04-03 12:00:00 |   11  |   1
    2     | 2014-04-02 12:00:00 | NULL  |   2     <--

The part I don't manage is to get the last row.

Cheers

Upvotes: 0

Views: 188

Answers (4)

wumpz
wumpz

Reputation: 9201

This is a solution to your request:

select 
r.runtimeId,r.time,
(select temps from temps where runtimeId=r.runtimeId and itemId=data.itemId) as temps,
data.itemId
from 
runtimes r, 
(select distinct itemId from temps) data
order by r.runtimeid, data.itemid

It gives you the wanted result.

http://sqlfiddle.com/#!2/bc9e1/5

runtimeId | time                | temps | itemId 
    1     | 2014-04-02 12:00:00 |   10  |   1
    1     | 2014-04-02 12:00:00 |   20  |   2
    2     | 2014-04-03 12:00:00 |   11  |   1
    2     | 2014-04-02 12:00:00 | NULL  |   2    <<<<<  preserves itemId 2

Especially the runtimeId=2 and itemId=2 result row. It selects all runtimes with all itemIds.

I inserted an order by to get the sorting you suggested in your request.

Upvotes: 1

crthompson
crthompson

Reputation: 15875

A left join will show you all the records from the temps table as well as the records for the runtimes table.

select
  *
from
  runtimes rt
  left join temps t on rt.runtimeId = t.runtimeId

You've indicated that you want to see itemId 2 that matches runtimeId 2.

Nothing joins in your data this way however, so the 2 indicated with the arrow would be null.

Your result would be:

runtimeId | time                | temps | itemId 
    1     | 2014-04-02 12:00:00 |   10  |   1
    1     | 2014-04-02 12:00:00 |   20  |   2
    2     | 2014-04-03 12:00:00 |   11  |   1
    2     | 2014-04-02 12:00:00 | NULL  |   NULL

For a cartesian product that will show itemId regardless of its relation to the other table, see @wumpz answer

Upvotes: 2

Mohini Mhetre
Mohini Mhetre

Reputation: 919

try this one

select r.runtimeid,r.time, t.temps, t.itemId from runtime r left join temps t 
on r.runtimeid=t.runtimeid

Upvotes: 0

Alex
Alex

Reputation: 11579

select r.runtimeId, r.time, t.temps, t.itemId from runtimes r
left outer join temps t on r.runtimeId = t.runtimeId

Upvotes: 0

Related Questions