By Nitesh


2018-02-13 13:15:03 8 Comments

I have two tables, X and Y :

mysql> select * from X;
+------+------+
| id   | colX |
+------+------+
| 2001 |    0 |
| 2002 |    0 |
| 2003 |    0 |
| 2004 |    0 |
| 2005 |    0 |
| 2006 |   10 |
| 2007 |   10 |
+------+------+

mysql> SELECT * FROM Y;
+------+------+-------+
| id   | colY | score |
+------+------+-------+
| 2001 |   10 |     3 |
| 2004 |    0 |    12 |
| 2005 |    0 |    15 |
| 2007 |    0 |     1 |
+------+------+-------+

I need the following result but without the subquery :

mysql> SELECT * FROM X LEFT JOIN Y ON X.id = Y.id WHERE colX=0 AND X.id NOT IN (SELECT id FROM Y WHERE colY > 0);
+------+------+------+------+-------+
| id   | colX | id   | colY | score |
+------+------+------+------+-------+
| 2002 |    0 | NULL | NULL |  NULL |
| 2003 |    0 | NULL | NULL |  NULL |
| 2004 |    0 | 2004 |    0 |    12 |
| 2005 |    0 | 2005 |    0 |    15 |
+------+------+------+------+-------+
  • colX must be 0
  • if id is present in table Y and colY = 0 then give corresponding score
  • if id is not present in table Y give score = NULL

I tried the following query but id 2001 shows up :

mysql> SELECT * FROM X LEFT JOIN Y ON X.id = Y.id AND Y.colY = 0 WHERE colX=0;
+------+------+------+------+-------+
| id   | colX | id   | colY | score |
+------+------+------+------+-------+
| 2001 |    0 | NULL | NULL |  NULL |
| 2002 |    0 | NULL | NULL |  NULL |
| 2003 |    0 | NULL | NULL |  NULL |
| 2004 |    0 | 2004 |    0 |    12 |
| 2005 |    0 | 2005 |    0 |    15 |
+------+------+------+------+-------+

1 comments

@Gordon Linoff 2018-02-13 13:22:03

I think this is the logic that you want:

SELECT *
FROM X LEFT JOIN
     Y
     ON X.id = Y.id 
WHERE x.colX = 0 AND (Y.colY = 0 OR Y.colY IS NULL)

Demo

@Nitesh 2018-02-13 13:31:07

Thanks! Exactly what I needed.

Related Questions

Sponsored Content

35 Answered Questions

[SOLVED] Should I use the datetime or timestamp data type in MySQL?

37 Answered Questions

[SOLVED] How to import an SQL file using the command line in MySQL?

32 Answered Questions

[SOLVED] What is the difference between "INNER JOIN" and "OUTER JOIN"?

4 Answered Questions

[SOLVED] Deleting rows with MySQL LEFT JOIN

20 Answered Questions

[SOLVED] How to reset AUTO_INCREMENT in MySQL?

16 Answered Questions

[SOLVED] LEFT JOIN vs. LEFT OUTER JOIN in SQL Server

3 Answered Questions

4 Answered Questions

[SOLVED] SQL SORT BY ALTERNATE YEAR

2 Answered Questions

[SOLVED] Finding an element with max. no of appearing in SQL

Sponsored Content