CODEWITHSUNDEEP
mysql
ccdavies
ccdavies

Reputation: 1606

Check if JOIN exists and if column does not equal if it does

I have two tables

channel_titles
channel_grid

Inside channel_grid, there is a column named col_id_15.

I need to query for entry_id where

  1. Either the entry_id does not exist in the channel_grid table
  2. OR if the entry_id does exist in the channel_grid table, the col_id_15 is not equal to 1

I had thought that the following could work. 

SELECT ct.entry_id
FROM channel_titles AS ct
LEFT JOIN channel_grid AS g ON g.entry_id = ct.entry_id                      
WHERE g.col_id_15 != '1'

Is anyone able to help?

Upvotes: 1

Views: 148

Answers (3)

Paul Tofunmi
Paul Tofunmi

Reputation: 530

SELECT ct.entry_id
FROM channel_titles AS ct
LEFT JOIN channel_grid AS g ON g.entry_id = ct.entry_id                      
WHERE g.col_id_15 <> 1 OR g.entry_id IS NULL

Well, I think your question can be further simplified for easier understanding.

Upvotes: 0

Maksym Fedorov
Maksym Fedorov

Reputation: 6456

Your query seems right. But if you want to get all entry_id which don't exist in the channel_grid table you must check g.entry_id is equal NULL.

SELECT ct.entry_id
FROM channel_titles AS ct
LEFT JOIN channel_grid AS g ON g.entry_id = ct.entry_id                      
WHERE g.entry_id IS NULL OR g.col_id_15 != '1'

That conditional is required because comparison any value with NULL always return NULL. For example:

SELECT NULL != '1' // return NULL

Upvotes: 1

Alexandre Juma
Alexandre Juma

Reputation: 3323

According to your two requirements, this should work:

SELECT ct.entry_id
FROM channel_titles AS ct
LEFT JOIN channel_grid AS g ON ct.entry_id = g.entry_id                      
WHERE g.entry_id IS NULL OR g.col_id_15 <> '1'

Just make sure that g.col_id_15 is a varchar, because the single quotes treat it as a string and not a number. If it's actually an INT, you can just take out the single quotes.

Upvotes: 1

Related Questions