selecting from two tables where values not in a third table

[gnznroses]

trying to determine if there's one query that can do all of this, or if i need to do one query to get the main results, and then loop those doing a second query on each result to remove specific ones.

 

 

let's say i have tables like this (example only...):

 

users

user_transactions

hated_users

 

and i wanted to select from both users and user_transactions

WHERE users.id = user_transactions.user_id

AND users.active = 1

 

but i wanted to exclude users who are in the hated_users table, which, let's say, were set up like this

admin_id

user_id

 

and i wanted to exclude users, basically like this (let's say i'm admin_id 2)

WHERE hated_users.user_id = users.id AND hated_users.admin_id = 2

 

 

my thought was to use NOT IN, but i've never used it and it seems like this is too complex for it. because i have to check that the user/admin pair is not in that table (not just check for a user id or an admin id, but that I hate those speific users).

 

 

is this possible? or do i hve to get a list of users and then loop them all, excluding those that i don't want to see listed?

thanks

[teng84]

this is mysql not php

 

anyways

not in

WHERE users.id = user_transactions.user_id
AND users.active = 1
and onotherfieldhere not in(select id from tablename where condition here)

[gnznroses]

this is mysql not php

 

anyways

not in

WHERE users.id = user_transactions.user_id
AND users.active = 1
and onotherfieldhere not in(select id from tablename where condition here)

 

thanks, i think i understand that. i'll try it out.

 

sorry if this is the wrong section. i had this subforum bookmarked and then just saw that it was called "php and myql" so i thought i was in the right place.

[Psycho]

You can absolutely do it in one query. I'm usually better with this when I have the appropriate tables to work with, but try the query below.

 

The LEFT JOIN will ensure that all the records from the first two tables will be returned and the hated_users values will be returned for those records included in those tables, but for records that do not have matches to the hated_users table those values will be returned as NULL.

 

So, for a user who does not exist in the hated_users table, that record will be returned with the column for hated_users.user_id, but it will be NULL and not equal to the actual user ID. Therefore, the last WHERE clause should filter out those users that are in the hated_users table.

 

SELECT *

FROM users u
JOIN user_transaction ut
 ON u.id = ut.user_id
LEFT JOIN hated_users hu
 ON u.id = hu.user_id

WHERE u.active = 1
 AND hu.user_id <> u.user_id

 

EDIT: Scratch that, I think teng's solutin is more elegant:

SELECT *

FROM users u
JOIN user_transaction ut
  ON u.id = ut.user_id

WHERE u.active = 1
  AND u.id NOT IN (SELECT user_id FROM hated_users)

[gnznroses]

k, thanks guys

 

one other question, is a syntax like this one below equivalent to the systax you are using? is one faster or better than the other?

 

SELECT * FROM users u, user_transaction ut

WHERE u.id = ut.user_id

AND u.active = 1

AND u.id NOT IN (SELECT user_id FROM hated_users)

 

that's how i've been doing them. not using JOIN and ON but just WHERE.

 

 

 

oh, and i think it would need to be like this:

SELECT *

 

FROM users u

JOIN user_transaction ut

  ON u.id = ut.user_id

 

WHERE u.active = 1

  AND u.id NOT IN (SELECT user_id FROM hated_users WHERE hated_users.admin_id = 2)

[Psycho]

k, thanks guys

 

one other question, is a syntax like this one below equivalent to the systax you are using? is one faster or better than the other?

 

SELECT * FROM users u, user_transaction ut

WHERE u.id = ut.user_id

AND u.active = 1

AND u.id NOT IN (SELECT user_id FROM hated_users)

 

that's how i've been doing them. not using JOIN and ON but just WHERE.

 

 

 

oh, and i think it would need to be like this:

SELECT *

 

FROM users u

JOIN user_transaction ut

  ON u.id = ut.user_id

 

WHERE u.active = 1

  AND u.id NOT IN (SELECT user_id FROM hated_users WHERE hated_users.admin_id = 2)

 

I prefer using JOINs. If you want to see which is faster - just test it. Put each one in a loop that runs n times and get the time before and after the loop. Although you can do much, much more with JOINs that you can't do by using the WHERE clause to join tables - for instance the LEFT JOIN that I used above.

 

Yes, you need to add the WHERE clause as you have it, I didn't catch that requirement in your original post.