Can't get expected result using left join and on condition

Question

Running the following scripts, you will get 2 tables with records.

-- ----------------------------
-- Table structure for data
-- ----------------------------
DROP TABLE IF EXISTS `data`;
CREATE TABLE `data` (
  `id` int(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of data
-- ----------------------------
INSERT INTO `data` VALUES ('1');
INSERT INTO `data` VALUES ('2');

-- ----------------------------
-- Table structure for status
-- ----------------------------
DROP TABLE IF EXISTS `status`;
CREATE TABLE `status` (
  `id` int(255) DEFAULT NULL,
  `status` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of status
-- ----------------------------
INSERT INTO `status` VALUES ('1', '0');
INSERT INTO `status` VALUES ('1', '1');

Table data

+--------+--------+
|  id    | more...|
|--------|--------+
|  1     |        |
|  2     |        |
+--------+--------+

Table status

+--------+--------+
|  id    | status |
|--------|--------+
|  1     |  0     |
|  1     |  1     |
+--------+--------+

I need select data of table data excluded items which status is 0 after refer to status.

Wrong try 1

I write the following SQL, and get the following result. It's not the expected result because the item id=2 is also needed.

select * from data d LEFT JOIN status s on d.id=s.id where s.status=1

+--------+--------+--------+
|  id    |  id1   | status |
|--------|--------|--------+
|  1     |  1     |  1     |
+--------+--------+--------+

Wrong try 2

Then I change where to on, and get the following result which looks good.

select * from data d LEFT JOIN status s on d.id=s.id and s.status=1

+--------+--------+--------+
|  id    |  id1   | status |
|--------|--------|--------+
|  1     |  1     |  1     |
+--------+--------+--------+
|  2     | (Null) | (Null) |
+--------+--------+--------+

Try 3

Table status has two items now. If I delete the item (id=1,status=1) of table status , what will be the result ?

select * from data d LEFT JOIN status s on d.id=s.id and s.status=1

+--------+--------+--------+
|  id    |  id1   | status |
|--------|--------|--------+
|  1     | (Null) | (Null) |
+--------+--------+--------+
|  2     | (Null) | (Null) |
+--------+--------+--------+

In this case, I expect the item id=1 should not be here . The expected result is

+--------+--------+--------+
|  id    |  id1   | status |
+--------+--------+--------+
|  2     | (Null) | (Null) |
+--------+--------+--------+

Why does the item id=1 appear in the result ? Can't it resolve my issue using on status=1 ?

It's not clear what you are asking for. It could be you want items from data where there is no record in status and any item from data where there is no 0 status value in status. — P.Salmon, May 18 '20 at 11:35
Does this answer your question? [Left Outer Join doesn't return all rows from my left table?](https://stackoverflow.com/questions/4707673/left-outer-join-doesnt-return-all-rows-from-my-left-table) — philipxy, May 19 '20 at 02:22
Please in code questions give a [mre]--cut & paste & runnable code, including smallest representative example input as code; desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. Give the least code you can give that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) For SQL that includes DBMS & DDL (including constraints & indexes) & input as code formatted as a table. [ask] Stop trying to code your overall goal & explain what you expected instead from the given code & why. — philipxy, May 19 '20 at 02:24
Please ask 1 specific researched non-duplicate question. Please clarify via edits, not comments. When you get a result that you don't expect, find out what your misunderstanding is.--Isolate the first subexpression that doesn't output what you expect (which includes confirming that others all give what you expect) & its input & output. Don't give a bunch of wrong stabs in the dark & a bunch of uninvestigated/researched wonderings & give up. — philipxy, May 19 '20 at 02:24

forpas · Accepted Answer · 2020-05-18T12:08:57.450

1

A LEFT JOIN returns all the rows of the left table.
Use NOT EXISTS:

select d.* from data d
where not exists (
  select 1 from status s
  where s.id = d.id and s.status = 0
)

Or with NOT IN:

select * from data 
where id not in (select id from status where status = 0)

See the demo.

edited May 18 '20 at 12:08

answered May 18 '20 at 11:55

forpas

160,666
10
38
76

Thanks for your reply. Your two SQL clauses can filter the records of table `data` correctly. If I also need the columns of table `status`, is it possible to do it with a simple way? `select d.*,s.* from data d` seems not working. – Does May 19 '20 at 01:10
Do you mean this: `select d.*, s.* from data d inner join s on s.id = d.id where d.id not in (select id from status where status = 0)` ? – forpas May 19 '20 at 08:02

Snuka · Answer 2 · 2020-05-20T10:44:14.417

0

Perhaps post your expected answer, but to return values in data where status is not zero:

Select id 
from data d
Left join status s on d.id = s.id
Where ((s.status <> 0) or (s.status isnull))

edited May 20 '20 at 10:44

answered May 18 '20 at 11:15

Snuka

1
3

That's not my need. The records that have no status also be included into results. – Does May 19 '20 at 00:50

score 0 · Answer 3 · answered May 19 '20 at 05:15

I've resolved my issue with the following SQL. I have learned my case is not a typical LEFT JOIN scenario, so just putting a condition status=1 to where or on can't resolve it directly.

SELECT
    *
FROM
    data d
LEFT JOIN status s ON d.id = s.id
WHERE
    s.status = 1
OR ISNULL(s.status)

Can't get expected result using left join and on condition

Wrong try 1

Wrong try 2

Try 3

3 Answers3