Using an intermediate table for something that "doesn't" match!

[jtakkinen_82]

Hey, me again...

 

Now I got this problem where I need to to use an intermediate table in order to find something that cannot be found from there... let me explain:

 

I got these tables...

-------

users

-------

user_id (pk)

etc...

 

-------

teams

-------

team_id (pk)

etc...

 

------------

teams_users (to bind those 2 tables together)

------------

user_id (fk)

team_id (fk)

 

Now of course I can find info from the tables using that intermediate table and some known value... BUT, what I'd need to do now is to find all the user_id's from the users table that CAN NOT be found from the intermediate tables (teams_users) user_id column. Is it possible? I got something like this but it obviously doesn't work:

 

SELECT users.user_id, users.username

FROM users JOIN

teams_users ON users.user_id = teams_users.user_id

WHERE teams_users.user_id != users.user_id

 

Come on gurus, I know you can do it if it's possible! 

[rhodesa]

How bout this:

 

SELECT user_id, username

FROM users

WHERE user_id NOT IN (

    SELECT DISTINCT(user_id)

    FROM teams_users

)

[jtakkinen_82]

How bout this:

 

SELECT user_id, username

FROM users

WHERE user_id NOT IN (

    SELECT DISTINCT(user_id)

    FROM teams_users

)

 

YES! That's got it. How the hell didn't I think of that? Well, that's what you get when you're a n00b with MySQL like me. Thanks again, man!

[aschk]

Yuck, DISTINCT, rhodesa you're an awful man...

 

How about:

 

SELECT user_id
      ,username
FROM users u
LEFT JOIN teams_users tu ON u.user_id = tu.user_id
WHERE tu.user_id IS NULL
GROUP BY u.user_id, username

 

 

[jtakkinen_82]

Yuck, DISTINCT, rhodesa you're an awful man...

 

How about:

 

SELECT user_id
      ,username
FROM users u
LEFT JOIN teams_users tu ON u.user_id = tu.user_id
WHERE tu.user_id IS NULL
GROUP BY u.user_id, username

 

 

 

What's wrong with using DISTINCT? Sorry for asking so many questions but I really would like to know .

[aschk]

I read about it a while back, http://www.onlamp.com/pub/a/onlamp/2004/09/30/from_clauses.html which stemmed my thought and confirmed what I suspected for a while. It's an unhealthy function in my opinion and encourages bad practices for those who should be writing correct ANSI JOINs.

[jtakkinen_82]

I read about it a while back, http://www.onlamp.com/pub/a/onlamp/2004/09/30/from_clauses.html which stemmed my thought and confirmed what I suspected for a while. It's an unhealthy function in my opinion and encourages bad practices for those who should be writing correct ANSI JOINs.

 

Ok, well I'll ditch using distinct then and try doing it your way.

[aschk]

WHOOP WHOOP! 

[rhodesa]

Looks like my plot to slow down the internet by getting everyone to use DISTINCT() has been foiled 

[aschk]

Indeed you have been foiled, my arch-nemesis. "BACK TO THE BAT CAVE ROBIN!"

 

note: DISTINCT ISN'T inherently bad, I'm just not a fan, it's almost like a personal grudge of mine, sorry...

[fenway]

note: DISTINCT ISN'T inherently bad, I'm just not a fan, it's almost like a personal grudge of mine, sorry...

Actually, it is... unless it's inside COUNT().

[rhodesa]

I understand that the JOIN would be better. But would the nested SELECT run faster without the DISTINCT?

 

So is this:

SELECT username FROM users WHERE user_id NOT IN (SELECT user_id FROM teams_users)

better then this?

SELECT username FROM users WHERE user_id NOT IN (SELECT DISTINCT(user_id) FROM teams_users)

[fenway]

Hard to say... that's a dependent subquery -- only run once for the outer query -- and obviously the DISTINCT would make that subquery take longer.  However, NOT IN() would have to check a longer list without the DISTINCT.  They're both bad.