CODEWITHSUNDEEP
mysqlsql
Theraloss
Theraloss

Reputation: 700

Get records not present in another table with a specific user id

I've this table (a)

And this table (b)

Now I have to get all records from A which are not present in B (a.id not present as b.idDomanda) and where B.idUser is not 1. So In this case, it should return only id 2 from a, but it returns 1 and 2.

This is my Query

SELECT a.* FROM a LEFT JOIN b ON a.id=b.idDomanda WHERE ( b.idUser <> 1 OR b.idUser IS NULL ) GROUP BY a.id

Upvotes: 0

Views: 75

Answers (2)

geggleto
geggleto

Reputation: 2625

There should be no results given your data set.

All records from A which are not present in B (a.id not present as b.idDomanda) Given the test data set all of A is in fact IN b.idDomanda... even when filtering out userId = 1.

but as the previous person pointed out that is the query to check.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269873

You want to move the condition on b to the on clause:

SELECT a.*
FROM a LEFT JOIN
     b
     ON a.id = b.idDomanda and b.idUser <> 1
WHERE b.idUser IS NULL
GROUP BY a.id;

The group by suggests that you might want to use not exists instead:

select a.*
from a
where not exists (select 1
                  from b
                  where a.id = b.idDomanda and b.idUser <> 1
                 );

Upvotes: 1

Related Questions