CODEWITHSUNDEEP
mysql
Herbert
Herbert

Reputation: 85

MySQL: select elements in one table but not in another table, Im getting wrong results

I have the two following tables named tableA and tableB respectively

enter image description here

enter image description here

tableB contain names of all places.I want to select all Facilities and week from where are facility is in tableA but not in tableB. Table below shows what im intending to achieve

enter image description here

MySql query below is giving wrong results

 select tableA.Week ,tableB.Place 
    from tableA
    RIGHT JOIN tableB
    on tableA.Place <> tableB.Place

Upvotes: 0

Views: 154

Answers (1)

forpas
forpas

Reputation: 164069

You need a CROSS join of the distinct Weeks of TableA to TableB and NOT EXISTS to get the result that you want:

SELECT w.Week, b.Place
FROM (SELECT DISTINCT Week FROM TableA) w
CROSS JOIN TableB b
WHERE NOT EXISTS (SELECT 1 FROM TableA a WHERE a.Week = w.Week AND a.Place = b.Place)

See the demo.
Results:

Week Place
1 C
2 B
2 C

Upvotes: 1

Related Questions