Get all values from one table and check if they exists in other table, and brings data and NULL values

Asked Aug 26 '22 at 18:55

Active Aug 26 '22 at 19:00

Viewed 21 times

I need to get all values from one table and check if they exists in other table, and brings data and NULL values.

These are my tables and I use Mysql 1-table CS

| id_m  | id_cs  |
|:-----:|:------:|
| 1     | 12     |
| 2     | 12     |
| 3     | 12     |
| 4     | 77     |

2-table HC

| id_m | id_cs |  idu  |
|:----:|:-----:|:-----:|
| 1    | 12    |   9   |
| 2    | 12    |   9   |
| 2    | 12    |   88  |

And the result I'm looking for is:

Bring me all CS.id_m from CS that exists and NOT exists in HC where HC.id_cs=12 and HC.idu=9

I need this:

| id_m | id_cs |  idu  |
|:----:|:-----:|:-----:|
| 1    | 12    |   9   |
| 2    | 12    |   9   |
| 3    | 12    |   NULL|

Thanks for your help

edited Aug 26 '22 at 19:00

Jens

asked Aug 26 '22 at 18:55

2

Read about left join’s – Jens Aug 26 '22 at 19:01
Yes I tried but with no luck, when I add the "when" clause, it brings me only the exiting values without NULLS – ignacio dolcemascolo Aug 26 '22 at 19:04
1

Show us your LEFT JOIN attempt. – jarlh Aug 26 '22 at 19:07
Conditions on the second table should be in the ON clause, not WHERE. See https://stackoverflow.com/questions/47449631/return-default-result-for-in-value-regardless/47449788#47449788 – Barmar Aug 26 '22 at 19:17
Thank you All, I added all conditions in the ON statement instead of WHERE, now it works – ignacio dolcemascolo Aug 29 '22 at 17:01

0 Answers0