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?

Answers


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


Need Your Help

jQuery text() filter results before passing to another element

jquery text filter

The following is a simplified excerpt from my code: