What do I need in my query, perhaps NOT EXISTS or NOT IN?

Question

I have a table called review:

review_id   cat_id   public_or_private
1           1              0
11          2              2
12          3              1
13          4              2
14          5              2

And a table called category:

cat_id   user_id  
1        10298            
2        10299
3        10300
4        10299
5        10298

My statement:

$sql2 = "SELECT * 
        FROM review 
            INNER JOIN category ON review.cat_id = category.cat_id 
        WHERE review.public_or_private = 2";

This will give me the result:

review_id   cat_id   public_or_private   cat_id   user_id              
    11          2              2           2        10299
    13          4              2           4        10299
    14          5              2           5        10298

contacts table:

user_id    contact_id
10299       10298
10299       10300
10300       10298
10300       10301

How can I hone this further? : I only want results if user_id is not in the contact_id column of the contacts table.

So from user 10300 point of view the result should be:

review_id   cat_id   public_or_private   cat_id   user_id              
    11          2              2           2        10299
    13          4              2           4        10299

I believe I should be using NOT EXISTS or NOT IN but not sure how I should put it.

I find the usage of `user_id` and `contact_id` very confusing. What do these represent? What does any of it mean, and what does the query you want actually do? — KIKO Software, Jun 20 '19 at 11:28
Every user of the app has a `user_id`, which connects to the db through php and does stuff. The `contacts` table, in the `contact_id` column, holds contacts of `10300`, or whichever `user_id` has logged in. Hope that clears it up a bit... — CHarris, Jun 20 '19 at 11:36
So you want a query that, for any user who doesn't have any contacts yet, returns reviews with `public_or_private = 2` and also return the users who wrote those review. Is this for a single contactless user only? Or multiple? — KIKO Software, Jun 20 '19 at 11:50
Please research thoroughly before posting a question. The answers are often already here. — mickmackusa, Jun 20 '19 at 11:53
@KIKOSoftware Yes, correct. It would be for any user_id who logs in. I'm looking at alx' answer below, think he may have solved my problem. Tx. — CHarris, Jun 20 '19 at 11:55

score 1 · Accepted Answer · answered Jun 20 '19 at 11:34

1

I think this is what you want:

SELECT * 
        FROM review 
            INNER JOIN category ON review.cat_id = category.cat_id
            LEFT JOIN contacts ON contacts.user_id = category.user_id
        WHERE review.public_or_private = 2
        AND contacts.contact_id IS NULL

And here an example to support that statement: http://www.sqlfiddle.com/#!9/7bfb62/3

This also works:

SELECT * 
        FROM review 
            INNER JOIN category ON review.cat_id = category.cat_id
        WHERE review.public_or_private = 2
        AND NOT EXISTS(
            SELECT *
            FROM contacts
            WHERE contacts.user_id = category.user_id
       );

And here is updated fiddle: http://www.sqlfiddle.com/#!9/7bfb62/5

answered Jun 20 '19 at 11:34

alx

2,314
2
18
22

Unfortunately this is a duplicate question. – mickmackusa Jun 20 '19 at 11:49
2

@mickmackusa Almost all questions are duplicates in some way, what you're saying is that you _chose_ to close this question. – KIKO Software Jun 20 '19 at 11:52
Correct, almost all questions are duplicates. – mickmackusa Jun 20 '19 at 11:53
Yep, and other two questions are not marked as duplicate for some reason. – alx Jun 20 '19 at 11:56
@mickmackusa Marking questions as duplicates is done to improve the quality of the content of Stack Overflow, but which questions are actually closed, like this, is quite arbitrary. It's just not nice when you've spent time preparing an answer and someone else just removes the ability to give it. It forces you to answer quickly without being too thorough, and that is the opposite of what the goal of marking duplicates originally was. (not your fault, by the way) – KIKO Software Jun 20 '19 at 12:01
1

I'd say there are good and bad duplicates. Good ones -- when the same question is asked differently, which adds different use case, context, and search keywords. Bad ones are just that -- bad questions: too little or no googling before asking. This one is bad, BTW. – alx Jun 20 '19 at 12:12
1

@alx Excellent, thanks. Your query actually give me the one I WASN'T looking for :) but I modified it - I'm using the `AND NOT EXISTS` one - to `AND NOT EXISTS(SELECT * FROM contacts WHERE contacts.contact_id = category.user_id);` and now it gives the correct result. – CHarris Jun 20 '19 at 12:12
@mickmackusa I actually spent several hours trying to solve the problem myself before posting. And I don't post lightly because the question sometimes takes longer than the research you've been doing beforehand. – CHarris Jun 20 '19 at 12:15
That's a valid point too: sometimes you do not know enough to correctly ask the question. (I find myself in this position a little too often, TBH.) Probably that's because, as the saying goes, correct question already contains 50% of the answer. And SO is one huge cauldron of people and knowledge, without diversity by knowledge level. – alx Jun 20 '19 at 12:19

What do I need in my query, perhaps NOT EXISTS or NOT IN?

1 Answers1