Canman2005 Posted August 28, 2009 Share Posted August 28, 2009 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 Quote Link to comment Share on other sites More sharing options...
Jacobian Posted August 28, 2009 Share Posted August 28, 2009 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 <>) Quote Link to comment Share on other sites More sharing options...
Canman2005 Posted August 28, 2009 Author Share Posted August 28, 2009 hi nope that didnt seem to work, any other ideas? I tried <> and also != Quote Link to comment Share on other sites More sharing options...
DavidAM Posted August 28, 2009 Share Posted August 28, 2009 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" Quote Link to comment Share on other sites More sharing options...
Canman2005 Posted August 28, 2009 Author Share Posted August 28, 2009 thanks very much Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.