CODEWITHSUNDEEP
mysql
oaklander114
oaklander114

Reputation: 3383

How do I exclude NULL rows from join result

Below are the tables t1 and t2 that I want to generate the output table from. My struggle was to not include in the output the rows where y1 is NULL. 

+--------------+  +--------------+  
|      t1      |  |      t2      | 
+--------------+  +--------------+
| x0 | x1 | x2 |  |  x0  |   y1  |    
+--------------+  +--------------+
| a  | 10 | a  |  |  a   |  haha |
| b  | 11 |NULL|  |  b   |  NULL |
| c  | 12 | c  |  |  c   |  foo  |
| d  | 13 |NULL|  |  d   |  NULL |
+--------------+  + -----+-------+

+--------------+
|    output    |
+--------------+
|  x0  |  y1   |
+--------------+
|  a   |  haha |
|  c   |  foo  |
+--------------+

I tried:

SELECT t1.x2, t2.y1
FROM t1
LEFT JOIN t2 ON t1.x0 = t2.x0

Also: In some cases my rows don't say NULL but are just empty cells.

Upvotes: 0

Views: 81

Answers (2)

BentCoder
BentCoder

Reputation: 12740

This:

SELECT
t1.*,
t2.*
FROM t1, t2
WHERE
(t1.x2 IS NOT NULL AND t1.x2 <> '') AND (t2.y1 IS NOT NULL AND t2.y1 <> '')

Will give you:

'1', 'a', '10', 'a', '1', 'a', 'haha'
'1', 'a', '10', 'a', '4', 'd', 'foo'

Upvotes: 0

trnelson
trnelson

Reputation: 2763

You need:

SELECT t1.x2, t2.y1
FROM t1
LEFT JOIN t2 ON t1.x0 = t2.x0
WHERE y1 IS NOT NULL AND y1 <> ''

Upvotes: 1

Related Questions