CODEWITHSUNDEEP
mysqlsqlselectrecords
Urbycoz
Urbycoz

Reputation: 7421

How can I make an SQL query that handles missing records?

I've got two tables, which contain customer ages and heights.

Table: Ages
+-----------+------------+
|customerId |     age    |
+-----------+------------+
|     1     |     15     |
|     2     |     24     |
|     3     |     21     |
|     4     |     62     |
|     6     |     57     |
|     7     |     32     |
+-----------+------------+

Table: Heights
+-----------+------------+
|customerId |   height   |
+-----------+------------+
|     1     |     175    |
|     2     |     182    |
|     4     |     180    |
|     5     |     171    |
|     6     |     165    |
|     7     |     182    |
+-----------+------------+

I need to write a SELECT query that reads all the ages and heights. So something like this...

SELECT Ages.age, Heights.height 
FROM Ages INNER JOIN Heights ON Ages.customerId=Heights.customerId;

However (and here's the twist) due to sloppy record-keeping, there are missing records from both tables. (e.g. customerId 5 in Ages, and customerId 3 in Heights).

Is there a way to write the query so that it will still work, but return zeros whenever the data is missing?

i.e.

+-----------+------------+------------+
|customerId |     age    |   height   |
+-----------+------------+------------+
|     1     |     15     |     175    |
|     2     |     24     |     182    |
|     3     |     21     |     0      |
|     4     |     62     |     180    |
|     5     |     0      |     171    |
|     6     |     57     |     165    |
|     7     |     32     |     182    |
+-----------+------------+------------+

Upvotes: 2

Views: 76

Answers (4)

Federico J.
Federico J.

Reputation: 15892

Use a LEFT JOIN with an IF condition is NULL

SELECT Ages.age, IF (Heights.height IS NULL, 0, Heights.height) AS height
FROM Ages
LEFT JOIN Heights ON Ages.customerId=Heights.customerId;

OK ok, so quickly answer... above only will give you 0 as height. Problem is get also ages, but for that, better get all customers ids, left join ages and heights

Best answer is accepted answer, I leave this here because I learnt MySQL COALESCE() function, XD

Upvotes: 0

StuartLC
StuartLC

Reputation: 107247

MySql doesn't have full outer joins, but you can simulate one with a LEFT JOIN, followed by a RIGHT JOIN, combined with a UNION, which will combine + eliminate duplicates:

SELECT Ages.age, COALESCE(Heights.height, 0)
FROM Ages 
LEFT OUTER JOIN Heights ON Ages.customerId=Heights.customerId
UNION
SELECT COALESCE(Ages.age, 0), Heights.height 
FROM Ages 
RIGHT OUTER JOIN Heights ON Ages.customerId=Heights.customerId;

SqlFiddle Here

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269623

What you really need is a full outer join, but MySQL doesn't support that. Instead, get all the customers in a subquery and use left outer join:

select c.customerid, coalesce(a.age, 0) as age, coalesce(h.height, 0) as height
from (select customerid from ages union
      select customerid from heights
     ) c left outer join
     ages a
     on a.customerid = c.customerid left outer join
     heights h
     on h.customerid = c.customerid;

Upvotes: 1

Raphaël Althaus
Raphaël Althaus

Reputation: 60493

One way to go (they are others, as always)

select customerId, max(age), max(height)
from 
 (
  select customerId, age, 0 as height from Ages
  UNION
  select customerId, 0 as age, height from heights
 ) s
group by customerId;

see SqlFiddle

Upvotes: 4

Related Questions