I have 2 tables city_products that contains all products in the city and product_list that contains all the products that exist in different cities.I want a list of products not available in each city.
[![enter image description here][1]][1]
Table 1: city_prd
city product
------ -------
city 1 p1
city 1 p3
city 1 p2
city 2 p1
city 2 p5
Table 2: pdt_list
product
-------
p1
p2
p3
p4
p5
Desired output:
city product
------ -------
city 1 p4
city 1 p5
city 2 p2
city 2 p3
city 2 p4
I know its something to do with a cross join but I am not getting the exact answer
This is a variation of TheImpaler's answer, but it should work in all databases. Use a cross join to generate all combinations of cities and products. Then use left join (or a similar mechanism) to remove the ones that exist:
select c.city, p.product
from (select distinct city from city_prd) c cross join
pdt_list p left join
city_prd cp
on c.city = cp.city and p.product = cp.product
where cp.city is null;
I am also guessing that you have a cities table of some sort. You can use that instead:
select c.city, p.product
from cities c cross join
pdt_list p left join
city_prd cp
on c.city = cp.city and p.product = cp.product
where cp.city is null;