MySQL SELECT when not null

Question

I am trying to make a query where 3 tables are being used. The first, table1, is used to store id values for table2 and table3 as well as some other data. In some cases there are no values stored in either of table2 or table3, some cases involve one or the other, and some involve both.

The issue is that when there are not values in either of table2 or table3, SQL attempts to look up NULL values as follows:

SELECT table1.id, table2value, table3value
FROM table1, table2, table3
WHERE table1value1 = table2.id AND table1value2 = table3.id;

So in this query if table1value1 or table1value2 are NULL, the query will not work as it is obviously not a valid link. So I am wondering how I would go about finding only the values that exist.

Robin Castlin · Accepted Answer · 2012-09-05T07:22:41.450

2

Use INNER JOIN instead. It joins the tables together with the condition of existing data in all tables. Else that row won't be returned.

SELECT table1.id, table2.value, table3.value
FROM table1
INNER JOIN table2
ON table1.value1 = table2.id
INNER JOIN table3
ON table1.value2 = table3.id;

But if you require for only one of these 2 rows to have existing data, you can do like this:

SELECT table1.id, table2.value, table3.value
FROM table1
LEFT JOIN table2
ON table1.value1 = table2.id
LEFT JOIN table3
ON table1.value2 = table3.id;
WHERE table2.id IS NOT NULL OR table3.id IS NOT NULL

edited Sep 05 '12 at 07:22

answered Sep 05 '12 at 06:57

Robin Castlin

10,956
1
28
44

Thanks a bunch, the second part of your answer was exactly what i was looking for. – The Thirsty Ape Sep 05 '12 at 07:20

score 1 · Answer 2 · answered Sep 05 '12 at 06:57

1

Below query will return only matching rows with value exists in all 3 tables.

SELECT table1.id, table2value, table3value
FROM table1 inner join table2 on  table1value1 = table2.id
inner join table3 on table1value2 = table3.id;

answered Sep 05 '12 at 06:57

sel

4,982
1
16
22

score 0 · Answer 3 · answered Sep 05 '12 at 06:58

I think you need to use INNER JOIN, And by definition, the INNER JOIN keyword return rows when there is at least one match in both tables.

SELECT table1.id, table2.value, table3.value
FROM   table1 
          INNER JOIN table2
             ON table1.value1 = table2.id
          INNER JOIN table3
             ON table1.value2 = table3.id;

score 0 · Answer 4 · answered Sep 05 '12 at 06:58

0

try This:

SELECT table1.id, 
       table2value,
       table3value
FROM   table1
JOIN   table2
ON     table1value1 = table2.id
JOIN   table3
ON     table1value2 = table3.id;

answered Sep 05 '12 at 06:58

Joe G Joseph

23,518
5
56
58

score 0 · Answer 5 · answered Sep 05 '12 at 06:58

0

What about INNER JOIN

SELECT table1.id, table2value, table3value
FROM table1 INNER JOIN table2 ON table1.value1 = table2.id, 
INNER JOIN table3 ON table1.value2 = table3.id;

answered Sep 05 '12 at 06:58

rkosegi

14,165
5
50
83

score 0 · Answer 6 · answered Sep 05 '12 at 06:59

0

You should use INNER JOIN.

SELECT table1.id, table2value, table3value
FROM table1 
    INNER JOIN table2 ON table1value1 = table2.id
    INNER JOIN table3 ON table1value2 = table3.id;

answered Sep 05 '12 at 06:59

Himanshu

31,810
31
111
133

MySQL SELECT when not null

6 Answers6