Member Avatar for TNvol66

I was having trouble wording my title. I'll try my best to describe my problem. I'm a SQL newbie, so go easy on me..

I'm trying to match records that "doesn't exist" between two relational tables. For instance, if I have two tables.

Table 1:

Client_ID Client_Name

Table 2:

Purchase_ID Purchase_Name CLIENT_ID

As you see, I'm trying to match records from Table 2 in which a client doesn't have a purchase.

My thoughts:

SELECT CLIENT_ID

FROM CLIENT JOIN PURCHASE USING(CLIENT_ID)

GROUP BY CLIENT_ID 

NOT HAVING PURCHASE_ID

ORDER BY CLIENT_ID DESC;

Here is some data to further more explain what I'm trying to accomplish.

Ok for instance.

Client_ID    Client_Name
1            Bob
2            Dan
3            Joe
Purchase_ID     Purchase_Name      CLIENT_ID
1               Shelf              2
2               Book               1

So, in this case I want to find what client didn't make a purchase.

Which would be Client 3, Joe.

Obviously what I've coded is wrong, I'm just looking for basic guidence on how to match records in this instance. Maybe because I'm been working for 6 hours straight and I'm overlooking, I'm not sure. Thanks in advance for your help.

Edited by TNvol66 because: n/a
  • 3 Contributors
  • 7 Replies
  • 138 Views
  • 2 Days Discussion Span
  • Latest Post Latest Post by hfx642
Member Avatar for simplypixie

In MySQL I would use:

SELECT client_id FROM client WHERE client_id NOT IN (SELECT client_id FROM purchase) ORDER BY client_id DESC
Edited by simplypixie because: n/a
Member Avatar for hfx642

Well... What I would do, is...

Select * From Client
Where Client_ID in
( Select Client_ID From Client
  MINUS
  Select Client_ID From Purchase
);

I REALLY don't like to use "NOT in"!!!

Edited by hfx642 because: n/a
Member Avatar for TNvol66

Both look promising, but it's returning nothing for me. Very weird..

Figured it out... Works perfect! Thanks all for your help!

Edited by TNvol66 because: n/a
Member Avatar for simplypixie

hfx642 - instead of just writing this

I REALLY don't like to use "NOT in"!!!

Why not explain why as well.

Member Avatar for hfx642

Efficiency!
It is much easier and quicker to find something which is IN a list,
than it is to find something which is NOT IN a list.
My method above can use indexes (if available),
while the previous method requires a full table scan.

I don't mean to sound rude, but...
If anyone starts to argue my points... I don't care!
I'm just telling you what I have observed from my 20+ years of using Oracle.

Edited by hfx642 because: n/a
Member Avatar for simplypixie

hfx642, thank you for the explanation. I didn't say you were being rude I just don't think you can say something will that without expanding on the reasons why, which you have now done.

Member Avatar for hfx642

No, No, No... I didn't mean that! lol
I did NOT take it as, you thought that I was being rude.
What I meant was, that I don't mean to sound rude (because of MY comment)
If anyone starts to argue my points... I don't care!
No offence was taken by anyone here.
It's all good!!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.