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

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.

CodePudding user response：

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 )

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

CodePudding user response：

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