CODEWITHSUNDEEP
sqlmysql
user293838
user293838

Reputation:

Comparing two tables and finding mismatches

ive got two sql tables and i want to comepare them against eachother to find the ones that dont match.

i have something that works but for some reason missed out two records?

table flag_content contains: - userid - content_id

table topfive_order contains - nid - uid

i wish to find all records which topfive_order.nid doesnt exists in flag_content.content_id

my current query is:

select * from flag_content left join topfive_order topfive_order ON flag_content.content_id = topfive_order.nid WHERE topfive_order.nid is null

any tips or advice much welcome. im not too sure what im doing with left join.. so i assume that the couple of records which slip the net has something to do with that.

Upvotes: 1

Views: 5198

Answers (1)

The Scrum Meister
The Scrum Meister

Reputation: 30111

Turn the join around

SELECT *
FROM topfive_order topfive_order left join flag_content 
  ON flag_content.content_id = topfive_order.nid 
WHERE flag_content.content_id IS NULL

To find rows from the topfive_order table that do not exist in the flag_content table, you need to put the topfive_order at the LEFT of the LEFT JOIN.

For more on various join types, see Wikipedia

Upvotes: 1

Related Questions