[SOLVED] SELECT from a TABLE but ignore some rows

[Canman2005]

Hi all

 

I have 2 tables, one called `users` which looks like

 

id

name

 

and the other table called `user_list` which looks like

 

id

user_id (this field ties into the field `id` in the `users` table)

type

 

how can I do the following

 

1: select all rows from `users`

2: but ignore any rows from the `users` table if they appear in `user_list` (under `user_id`) and also have the `type` set to the value "X"

 

Sorry, hard to explain, hope it makes some sense

 

Any help would be great

 

thanks

 

dave

[Jacobian]

my SQL is a bit scratchy these days but something like this should do what your after:

 

SELECT u.id, u.name

FROM users u, user_list l

WHERE u.id <> l.user_id AND l.type = "X"

 

you'll get the ID and NAME rows from the users table

where the users id is different from user_list user_id and the user_list type is equal to whatever you wanted it to be...

 

only thing im not 100% about is the SQL not equal to symbols.. (could be != instead of <>)

 

 

[Canman2005]

hi

 

nope that didnt seem to work, any other ideas? I tried <> and also !=

[DavidAM]

My first reaction would be something like this:

SELECT u.id, u.name
FROM users u
WHERE NOT EXISTS (SELECT * FROM user_list l WHERE u.id = l.user_id AND l.type = "X")

 

although I think an outer join is the more acceptable method:

 

SELECT u.id, u.name
FROM users u LEFT JOIN user_list l ON u.id = l.user_id
WHERE l.user_id IS NULL OR l.type <> "X"

 

Either one of those should get you all rows in users that are not in user_list as well as the ones that are in user_list with a type other than "X".  If you only want rows that are in both tables with the type not equal to "X", try a straight join:

 

SELECT u.id, u.name
FROM users u JOIN user_list l ON u.id = l.user_id
WHERE l.type <> "X"

 

 

[Canman2005]

thanks very much