Removing rows using join and skip some of them - MySQL

Question

I'm having a hard time to use a MySQL query.

If I do the following:

SELECT ads.id_ad 
FROM tab_ads ads, tab_orders orders 
WHERE (ads.id_user = 111 AND ads.id_ad = orders.id_ad)

It gives me the correct answer, which is: "show me the ads belonging to user 111 that have been sold"

What I want is to remove all ads, except the ads that have been sold.

So I change the equal to not equal in the where criteria:

SELECT ads.id_ad 
FROM tab_ads ads, tab_orders orders 
WHERE (ads.id_user = 111 AND ads.id_ad != orders.id_ad)

And it gives me trash results.

Ex: I have three tables:

tab_ads
id_ad - title - etc.
01 - title1
02 - title2
03 - title3

tab_orders
id_order - id_ad - amount - etc.
XX          - 01     -  $10

tab_users
id_user - name - etc.
110 - Dr. Jivago - etc.
111 - Sherlock - etc.

I need to remove all ads from 111 (Sherlock) where there's no sell. So I need to remove ad 02 - title2 and 03 - title3 because ad 01 - title1 has been sold and I need to keep it for future consultation.

So, you need to return those records from tab_ads that are not in the orders table. That's what the duplicate topic shows you how to do. — Shadow, Aug 08 '17 at 19:23

ScaisEdge · Accepted Answer · 2017-08-08T19:48:15.287

0

You could use not in and a subselect

  select ads.id_ad 
  FROM tab_ads
  where ads.id_ad  not in (
      SELECT ads.id_ad 
      FROM tab_ads ads
      INNER JOIN  tab_orders orders on ads.id_user = 111 AND ads.id_ad = orders.id_ad)
      )
  AND tab_ads.id_user =  111

edited Aug 08 '17 at 19:48

answered Aug 08 '17 at 19:20

ScaisEdge

131,976
10
91
107

Thanks @scaisEdge, but I'm having exactly the same results. – Raphael Aug 08 '17 at 19:27
seems strange to me ... explain better .. the query should return all the id_ad not in subselect .... if your subselect return the id_ad sell .. you should have the not sell – ScaisEdge Aug 08 '17 at 19:28
I edited my question. – Raphael Aug 08 '17 at 19:36
answer update .. adding filter for 111 – ScaisEdge Aug 08 '17 at 19:40
Still a lot of trash: Showing rows 0 - 24 (93765760 total, Query took 0.0076 seconds.). To me the query looks pretty obvious, I don't have any clue why I'm having this answer. – Raphael Aug 08 '17 at 19:45
check for you real data content .. the answer you provided have no sense ..anyway answer update with correct table name and explicit join sintax – ScaisEdge Aug 08 '17 at 19:46
SOLVED! I was selecting "FROM tab_ads, tab _orders" which was giving the wrong results. Thanks. – Raphael Aug 08 '17 at 19:51

Removing rows using join and skip some of them - MySQL

1 Answers1