How get record for this specific case?
I have three tables
Person (id, name, email) Address (id, Person_id, address, verified) Person_Claimed (id, Person_id, name, email)
We add records in Person and Address table manually. When someone see his/her profile online, he/she claimed his/her profile and a entry is created in Person_Claimed Table.
Now I want to get records of persons who have not claimed their profiles AND also their addresses are not verified.
How to join these tables?
Assuming verified is not null and 0 means 'not verified'
SELECT p.* FROM Person p INNER JOIN Address a ON (a.Person_id = p.id ) LEFT JOIN Person_Claimed pc ON (pc.Person_id = p.id) WHERE a.verified =0 AND pc.id IS NULL
If person can have multiple not verified addresses, you may want to add GROUP BY p.id (or SELECT DISTINCT instead of SELECT