CODEWITHSUNDEEP
mysqlsql
Elyas Nice
Elyas Nice

Reputation: 43

SQL Check if one of record is not exists with join other table

I have tried and searched for the solution but found anything. I want to make a SQL Code which Check if one of a records is not existing in another table.

SQL Tables:

Table name: ads
╔═══════╦═════════╗
║ ad_id ║ user_id ║
╠═══════╬═════════╣
║  1    ║ Jeff    ║
║  2    ║ Jeff    ║
╚═══════╩═════════╩

Second Table name: premium
╔═══════╦═════════╗
║ ad_id ║ user_id ║
╠═══════╬═════════╣
║  1    ║ Jeff    ║

I want to Check the table Ads if the user_id = Jeff has not an record in table Premium. Barely it should show me in that case ad_id = 2

What I have tried is:

SELECT * FROM ads 
INNER JOIN premium ON premium.ad_id = ads.id 
WHERE premium.user_id = 'Jeff'

Upvotes: 1

Views: 1979

Answers (4)

Ruslan Valeev
Ruslan Valeev

Reputation: 1907

I would propose this:

SELECT * FROM ads 
LEFT OUTER JOIN premium ON premium.ad_id = ads.id 
WHERE ads.user_id = 'Jeff' AND premium.ad_id IS NULL

Upvotes: 0

Николай Лубышев
Николай Лубышев

Reputation: 640

SELECT p.`ad_id` as ad_id, p.`user_id` as user_id
FROM `premium` p 
INNER JOIN `ads` a ON a.`user_id` = p.`user_id`
WHERE p.`user_id` = 'Jeff'
GROUP BY p.`user_id`
HAVING a.`ad_id` = MIN(a.`ad_id`)

MIN(a.ad_id) you are must replace with condition what record you are want to select from table ads

And so on. If you are want records Check if one of a records is not existing in another table so

SELECT COUNT(a.`ad_id`) as cnt
FROM `premium` p 
INNER JOIN `ads` a ON a.`user_id` = p.`user_id`
WHERE p.`user_id` = 'Jeff'
GROUP BY p.`user_id`
HAVING a.`ad_id` <> MIN(a.`ad_id`)

So, if cnt > 0 its mean what records are exists!

If you are have a strong relation within an p.ad_id === a.ad_id :

SELECT COUNT(a.`ad_id`) as cnt
FROM `premium` p 
INNER JOIN `ads` a ON a.`user_id` = p.`user_id`
WHERE p.`user_id` = 'Jeff' AND a.`ad_id` <> p.`ad_id`

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269443

I recommend not exists:

select a.*
from ads a
where not exists (select 1
                  from premium p
                  where p.ad_id = a.ad_id and p.user_id = a.user_id
                 );

This is almost a direct translation of your problem statement. In addition, with an index on premium(ad_id, user_id) it should also have very good performance characteristics.

Upvotes: 1

Barbaros &#214;zhan
Barbaros &#214;zhan

Reputation: 65105

You may use correlated subquery with NOT IN

SELECT a.*
  FROM ads a 
 WHERE a.ad_id NOT IN
( SELECT p.ad_id FROM premium p WHERE p.user_id = a.user_id and p.user_id = 'Jeff' );

SQL Fiddle Demo

Upvotes: 0

Related Questions