CODEWITHSUNDEEP
sql
charlie_cat
charlie_cat

Reputation: 1850

one table minus another

I am not sure on how to do this. I have three tables members table with 238871 members jobs table with 7713 entries trade table and with 231142 entries

if i take 238871 members minus what is in the trade table i get a difference of 7729. in the jobs table I have 7713 entries so there are 16 unaccounted for in the members table. all of trade is in jobs and members tables. but in members there a 16 that is not in jobs and trade.

How can I define those 16 members? thanks

Upvotes: 0

Views: 745

Answers (1)

Ben
Ben

Reputation: 52893

Actually, a left join gives you everything in jobsortedusers. Then everything in members that is also in jobsortedusers.

It sounds like you either want to reverse the order of your left outer join and do this instead:

select * 
  from membership.members as mm
  left outer join jobsorted.jobsortedusers as js
    on mm.memberid = js.memberid

This gets you everything in members and then everything in jobsortedusers that is also in members. As you say that every user is also a member this query returns you everything.

I can highly recommend reading this, which explains joins very well.

To answer your new question you want everything where the jobsortedusers id is null, i.e.

select mm.* -- every other column will be null
  from membership.members as mm
  left outer join jobsorted.jobsortedusers as js
    on mm.memberid = js.memberid
 where js.memberid is null

I don't know what to join trade on but in order to get everything that is not in either this table or your users table you need something like this:

select mm.* -- every other column will be null 
  from membership.members as mm
  left outer join jobsorted.jobsortedusers as js
    on mm.memberid = js.memberid
  left outer join trade t
    on mm.memberid = t.memberid
 where js.memberid is null
   and t.memberid is null

Upvotes: 2

Related Questions