[Resolved] Subquery Troubles...

[Brendan_Nolan]

I am new to making subqueries and have yet to manage to make one work. Could someone please let me know where I am going wrong?

I have a table of people, each of whom belong to an entity. Some of those people have a record in the system_users table. I want a query that will give me a list of people that DO NOT have a row in the system_users table. I know that the first query, if it was to work, would return all the people who were system users, but I thought I would see if that worked first, then try something like "NOT IN".

Here are a few of the queries that I have tried...

SELECT * FROM people WHERE entity_id='1' AND person_first_name <> '_main' AND person_id IN (SELECT person_id FROM system_users) ORDER BY person_first_name

SELECT FROM people WHERE entity_id='1' AND person_first_name <> '_main' AND (SELECT COUNT() FROM system_users WHERE people.person_id=system_users.person_id) < 0 ORDER BY person_first_name

The mysql_error() that I get pretty much always just says that I have an error near <wherever my subquery starts>

[LordShryku]

A join should do it for you. Your mysql version might not support subqueries, as I think only the development versions do right now. Try something like this

SELECT     p.* 
FROM       people p
LEFT JOIN  system_users su
ON         p.person_id = su.person_id
WHERE      p.entity_id='1' 
AND        p.person_first_name != '_main' 
AND        su.person_id IS NULL
ORDER BY   p.person_first_name