CODEWITHSUNDEEP
phpmysql
Stephen Wolfe
Stephen Wolfe

Reputation: 215

SQL Query for finding what results are not in a table

I am a little bit rusty on my SQL and would like some help with these queries that I am struggling getting my head round or even finding out if it is possible.

Hi so I have some tables;

clients,products,clientproducts

when a client opts in for a product that is added to clientproducts, using the clientid and the productid.

the easy query I want to run a query that will only show me products the client has opted in for.

the harder one I also want to run a query that will show me just the clients that haven't opted in for a product/s.

Thank you for your help in advance.

Upvotes: 0

Views: 57

Answers (4)

asifsid88
asifsid88

Reputation: 4701

Get all the products information where client_id = 1

select products.*
from products, clientproducts
where products.id = clientproducts.product_id
and clientproducts.client_id = 1

Get all the clients details who has not opted for any products

select *
from clients
where id not in (select client_id
            from clientproducts)

Upvotes: 0

Dipesh Parmar
Dipesh Parmar

Reputation: 27354

The easy query

SELECT * FROM clients LEFT JOIN products ON clients.id = products.client_id

The hard query

SELECT * FROM clients WHERE clients.id NOT IN (SELECT clients.id FROM clients LEFT JOIN products ON clients.id = products.client_id)

Upvotes: 0

John Woo
John Woo

Reputation: 263683

here, use LEFT JOIN

SELECT  a.*
FROM    products a
        LEFT JOIN clientproducts b
            ON a.productID = b.productID
        LEFT JOIN clients c
            ON  b.clientID = c.clientID AND
                c.ClientID = 'clientID HERE'
WHERE   c.client IS NULL

OR

SELECT  a.*
FROM    products a
        LEFT JOIN clientproducts b
            ON a.productID = b.productID AND
               b.ClientID = 'clientID HERE'
WHERE   b.productID IS NULL

Upvotes: 0

ImadBakir
ImadBakir

Reputation: 553

Query 1:

   SELECT table1.id1 FROM table1
    WHERE table1.id1 IN (SELECT table2.foreign_id FROM table2);

Query 2: 

      SELECT table1.id1 FROM table1
        WHERE table1.id1 NOT IN (SELECT table2.foreign_id FROM table2);

Upvotes: 2

Related Questions