Jump to content


Photo

inversing a selection when joining tables


  • Please log in to reply
3 replies to this topic

#1 boonamera

boonamera
  • New Members
  • Pip
  • Newbie
  • 7 posts

Posted 29 July 2006 - 06:58 PM

table 1 (users)
userid
username

table 2(payments)
paymentid
userid

I need two things
USERIDS of those who PAID. This works:
SELECT u.userid FROM users u JOIN payments ON u.userid = payments.userid;

USERIDS of those who haven't paid. This is my problem
I tried something along the lines of: but no luck
SELECT u.userid FROM users u LEFT JOIN payments ON u.userid = payments.userid LIMIT 99999 OFFSET ("SELECT ( (SELECT COUNT(*) FROM users)-(SELECT COUNT(*) FROM payments) ));
but no luck

anyone have an idea?



#2 shoz

shoz
  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 29 July 2006 - 07:03 PM

If people who haven't paid don't have an entry in the payments table, then the following should work.
SELECT
u.userid
FROM
users AS u
LEFT JOIN
payments
ON
u.userid = payments.userid
WHERE
payments.userid IS NULL


#3 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 29 July 2006 - 07:44 PM

Yeah, go with shoz's suggestion... I nearly fell of my chair when I saw those crazy subselects with that LIMIT clause. ;-)
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#4 boonamera

boonamera
  • New Members
  • Pip
  • Newbie
  • 7 posts

Posted 29 July 2006 - 08:01 PM

AWESOME IT WORKED
thanks shoz

lol hey I used the minimal knowledge to try and get it working :)






0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users