MYSQL - get data from table that are not paired with another table

user_17473998 0
Viewed 64

I cant figure out one sql query. I need to get data from table, that are not paired in another table. So my limited knowlage is not enought here.

Lets say we have 3 tables client - category - client_category

TABLE CLIENT

id name
1 client 1
2 client 2

TABLE category

id name
1 category 1
2 category 2
3 category 3

TABLE client_category

id id_client id_category
1 1 1
2 2 1
3 2 2
3 2 3

DESIRED OUTPUT

client category
client 1 category 2
client 1 category 3

This also need to work with more clients and categories.

mysql
2 Answers
SELECT client.name, category.name
FROM client
CROSS JOIN category
WHERE NOT EXISTS ( SELECT NULL
                   FROM client_category
                   WHERE client.id = client_category.id_client
                     AND category.id = client_category.id_category )

or

SELECT client.name, category.name
FROM client
CROSS JOIN category
LEFT JOIN client_category ON client.id = client_category.id_client
                         AND category.id = client_category.id_category
WHERE client_category.id IS NULL
user_101387340

You are interested in all possible links between client and category, that do not exist in client_category

SELECT
   c.name, y.name
FROM client c
CROSS JOIN category y
LEFT JOIN client_category cc ON cc.id_client = c.id AND cc.id_category = y.id
WHERE cc.id IS NULL
ORDER BY c.id, y.id

output:

name name
client 1 catgory 2
client 1 category 3

DBFIDDLE

user_7240390
Related