CODEWITHSUNDEEP
mysqlsql
MikeOscarEcho
MikeOscarEcho

Reputation: 548

Why is this query duplicating results?

I'm not very experienced when it comes to joining tables so this may be the result of the way I'm joining them. I don't quite understand why this query is duplicating results. For instance this should only return 3 results because I only have 3 rows for that specific job and revision, but its returning 6, the duplicates are exactly the same as the first 3.

          SELECT
               checklist_component_stock.id,
               checklist_component_stock.job_num,
               checklist_revision.user_id,
               checklist_component_stock.revision,
               checklist_category.name as category,
               checklist_revision.revision_num as revision_num,
               checklist_revision.category as rev_category,
               checklist_revision.per_workorder_number as per_wo_num,
               checklist_component_stock.wo_num_and_date,
               checklist_component_stock.posted_date,
               checklist_component_stock.comp_name_and_number,
               checklist_component_stock.finish_sizes,
               checklist_component_stock.material,
               checklist_component_stock.total_num_pieces,
               checklist_component_stock.workorder_num_one,
               checklist_component_stock.notes_one,
               checklist_component_stock.signoff_user_one,
               checklist_component_stock.workorder_num_two,
               checklist_component_stock.notes_two,
               checklist_component_stock.signoff_user_two,
               checklist_component_stock.workorder_num_three,
               checklist_component_stock.notes_three,
               checklist_component_stock.signoff_user_three
               FROM checklist_component_stock
               LEFT JOIN checklist_category ON checklist_component_stock.category
               LEFT JOIN checklist_revision ON checklist_component_stock.revision = checklist_revision.revision_num
               WHERE checklist_component_stock.job_num = 1000 AND revision = 1;

Tables structure:

checklist_category

i

checklist_revision

i

checklist_component_stock

i

Upvotes: 0

Views: 57

Answers (3)

MikeOscarEcho
MikeOscarEcho

Reputation: 548

As well as fixing Thorsten Kettner's suggestion, my foreign keys for the revisions was off. I was referencing the revision in checklist_component_stock.revision to checklist_revision.revision_num when instead I should have referenced it to checklist_revision.id.

Upvotes: 0

Thorsten Kettner
Thorsten Kettner

Reputation: 94914

The line

LEFT JOIN checklist_category ON checklist_component_stock.category

was certainly supposed to be something like

LEFT JOIN checklist_category ON checklist_component_stock.category = checklist_category.category

Most other dbms would have reported a syntax error, but MySQL treats checklist_component_stock.category as a boolean. For MySQL a boolean is a number, which is 0 for FALSE and != 0 for TRUE. So every checklist_component_stock with category != 0 is being connected to all records in checklist_category.

Upvotes: 2

Zafar Malik
Zafar Malik

Reputation: 6844

First check your query as join is missing in your first left join. Also If you just want to get unique rows from your query then use of distinct just after select...

Upvotes: 0

Related Questions