Query: 2 Tables, One Result... checking fields in each table against the other

[Rottingham]

Ok... I'm no GURU at SQl but I run a web server and write custom software. I'm at a stage where I want to limit the query results based on two tables, with one execute();

 

I have two tables,

people

{

  ID (AUTO_INCREMENT)

  First

  Last

  HeadofHouse

  etc...

}

 

family

{

  ID (AUTO_INCREMENT)

  HeadofHouse (THIS WILL EQUAL ID FROM 'people' TABLE)

  etc...

}

 

I want to do this in essence

 

sql->execute("SELECT Last, First, ID FROM people WHERE HeadofHouse='1' AND (people.ID != family.HeadofHouse) ORDER BY Last, First ASC");

 

This query is intended to pull all of the people flagged as the HeadofHouse, but not those who have already been added into the Family table.

 

Any help here? Thanks!

 

[btherl]

Try this:

 

SELECT Last, First, ID FROM people LEFT JOIN family ON (people.ID = family.HeadofHouse) WHERE people.HeadofHouse = 1 AND family.HeadofHouse IS NULL

 

A left join will put nulls in the right-hand table (family) when there is no matching row in that table.  So by checking for null, you can find those rows present in people but not in family.