mySQL DISTINCT and JOINS

Question

QUERY

SELECT DISTINCT(o.id) as `order_line`,
  `k`.`short_name` AS `market`,
  `jc`.`code` AS `job_status`,
  `j`.`order_number` AS `job_number`,
  CONCAT('ML', `l`.`supp`) AS `supp_number`,
  `jd`.`needed_by` AS `needed_by`,
  `jd`.`mastec_onjob` AS `mastec_onjob`,
  `jd`.`revised_crcd` AS `revised_crcd`,
  `e`.`lastname` AS `install supervisor`,
  `o`.`order_number` AS `po number`,
  `o`.`order_date` AS `ordered`,
  `o`.`qty_ordered` AS `qty_ordered`,
  `o`.`ship_date` AS `vendor ship`,
  `o`.`rcvd_date` AS `rcvd_date`,
  `o`.`qty_received` AS `qty_received`,
  `m`.`mastec_partno` AS `mastec_partno`,
  `v`.`long_name` AS `vendor_name`,
  `o`.`mm_notes` AS `mm_notes`,
  `o`.`pm_notes` AS `pm_notes`,
  `o`.`rc_notes` AS `rc_notes` 
FROM
  mm_orders as o 
  LEFT JOIN `material_lists` `l` 
    ON ((`l`.`id` = `o`.`ml_id`)) 
  LEFT JOIN `jobs` `j` 
    ON ((`l`.`job_id` = `j`.`id`)) 
  LEFT JOIN `clli` `c` 
    ON ((`j`.`clli` = `c`.`id`)) 
  LEFT JOIN `markets` `k` 
    ON ((`k`.`id` = `c`.`market`)) 
  LEFT JOIN `employees` `e` 
    ON ((`e`.`employee_number` = `j`.`install_supervisor`)) 
  LEFT JOIN `job_dates` `jd` 
    ON ((`l`.`job_id` = `jd`.`job_id`)) 
  LEFT JOIN `mat_r12` `m` 
    ON ((`o`.`ci_id` = `m`.`id`)) 
  LEFT JOIN `vendors` `v` 
    ON ((`v`.`id` = `m`.`vendor_id`)) 
  LEFT JOIN `ml_contents` `mc` 
    ON ((`l`.`id` = `mc`.`ml_id`)) 
  LEFT JOIN `job_status_codes` `jc` 
    ON ((`j`.`status` = `jc`.`id`)) 
WHERE o.qty_ordered > o.qty_received 
  AND `j`.`status` IN (1, 5, 8, 11, 12, 13, 14)
  AND `mc`.`mastec_stock` = 0 
ORDER BY `j`.`order_number`,
  `l`.`id`,
  `l`.`supp`,
  `o`.`vendor_id`;

I want the results to reflect only one row per o.id but that's not at all what I'm getting. When I remove the GROUP BY, I get 63391 rows and with it there, I only get 188 rows. When I run SELECT id FROM mm_orders WHERE qty_ordered > qty_received, I get the proper number of rows at 1249. Obviously, I am not the MySQL master that I thought I was. Is it my JOINs or have I just completely botched the query from one end to the other?

In case the hierarchy is unclear, from top to bottom: job-> material_list-> ml_contents. Multiple material_lists can be assigned the same job_id, multiple ml_contents are within the same material_list. The following tables have one-to-one relationships: job-> job_dates, ml_contents-> mm_orders. All the others should be self-explanatory but if there is a good jsFiddle type of tool for MySQL, I'd be glad to post the structures of each table.

QUERY UPDATED

Answer 1

It is the joins. You used inner joins, but maybe some orders don't have a material list or installer or whatever. Those orders will not be in the end result, causing it to drop down to 188 unique orders.

Furthermore, if the job for an order has multiple job dates, you will get that order in the result for every job date it has. That will cause the end result of 188 unique orders to be displayed as 63391 rows.

So, rather than applying grouping and distinct to try and solve it, you should first ask yourself what it is you want to query. Do you want to have a list of unique orders, or do you also want extra information? If the latter, be aware that the query contains for instance order lines, or job dates, and that the order information itself may be duplicated.