[SOLVED] joins and conditional logic

[aunquarra]

Okay, I have two tables I frequently join. One is a list of people, and the other is a list of details about those people.

 

I typically do the joins with the id number in the people table and a column with people IDs in the details table.

 

So it would be like...

 

SELECT `people`.*, `details`.* from `people` left join `details` on `people`.`id`=`details`.`people_id`....

 

The trick is, as you might guess by the way I'm building it, each people_id could have multiple rows in the details table. Typically, I only call on the details table when I'm looking at one specific person, so no biggie there. But in this case, I need to retrieve a list of people.

 

What I'm wanting to accomplish is to, without resource-heavy sub-selects, return only people who don't have a specific detail.

 

So like, if I'm looking for people who are unemployed, and the way I tell if a person is employed is to check and see if they have a row in the details table that has a `detail_category`='job', then to return a list of unemployed people, I have to check to see if they don't have such row.

 

The more I think about it, the more I think this isn't going to be something I can do with a simple join. Either I'm going to have to have PHP do the legwork or do a subselect. But I figured I'd ask the professionals...

[fenway]

Why not just add this condition to your ON clause and add an IS NULL on the details table in your WHERE clause?

[aunquarra]

Perfect!

 

Not sure why I didn't think of that, but thank you!