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

Thanks

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

“Plant” Bash Scripts into a tcl Script

bash unix tcl

I have a main tcl script which calls multiple bash scripts. But I want to have everything in one place. Is there a way to directly implement my bash scripts inside the tcl script (as procs?) and so...

Cant create Ruby on Rails app on Windows - Mysql error

mysql ruby-on-rails ruby-on-rails-3 install

After installing rubyinstaller i tried creating a new mysql app with mysql and i get the below error that has to do with mysql...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.