CODEWITHSUNDEEP
mysqlsql
Rauno
Rauno

Reputation: 636

SQL query to get all except where id is in array that comes from another table

I could use some help with some SQL. Part of a website that I need to do is a place where you can add/remove stores from users. Now the query to get the stores that have already been assigned to a certain user is simple, however I can't seem to figure out how to do the opposite. Meaning getting all that haven't been added to the user yet.

So here's a template of 1 of the tables.

    user_id  store_id
    1        11 
    1        12
    1        14
    2        15
    4        16

If I run this (that 1 will be php variable): SELECT store_id FROM store_user WHERE user_id= 1; I get the result like that: 

store_id
11
12
14

And the rest would be something like: SELECT * FROM store except where id = 11,12,14.

I would appreciate if anyone could help me with that last part and preferrably put it all in to 1 query.

Edit: for those who stumble here with a similar problem, this is what worked in the end:

SELECT * FROM store where id NOT IN (select store_id from store_user where user_id = 1)

Thanks for the help everyone!

Upvotes: 3

Views: 2728

Answers (2)

Shivanand Baste
Shivanand Baste

Reputation: 84

You can use IN or NOT IN

select store_id from store where id in (1,2....)
select store_id from store where id not in (1,2....)

Upvotes: 1

Abhishek Sharma
Abhishek Sharma

Reputation: 6661

use NOT IN

SELECT * FROM store where 
id NOT IN (select store_id from store_user where user_id = 1)

Upvotes: 0

Related Questions