Databases
StackOverflow2013
Postsdbo
POLeft Joins returns duplicate values
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POLeft Joins returns duplicate values
    primarykey
    data
    text
    <p>The result I receive:</p> <pre><code>id sku name GROUP_CONCAT(quantity_received) GROUP_CONCAT(item_cost) 4 00004 Antibacterial Wipes 50,14,25,309,50,14,25,309,50,14,25,309,50,14,25,309,50,14,25,309,50,14,25,309,50,14,25,309,50,14,25,309,50,14,25,309 3.29,3.29,3.29,3.49,3.29,3.29,3.29,3.49,3.29,3.29,3.29,3.49,3.29,3.29,3.29,3.49,3.29,3.29,3.29,3.49,3.29,3.29,3.29,3.49,3.29,3.29,3.29,3.49,3.29,3.29,3.29,3.49,3.29,3.29,3.29,3.49 </code></pre> <p>The result I want:</p> <pre><code>id sku name GROUP_CONCAT(DISTINCT quantity_received) GROUP_CONCAT(DISTINCT item_cost) 4 00004 Antibacterial Wipes 50,14,25,309 3.29,3.49 </code></pre> <p>The way I resolved this issue was placing DISTINCT in the quantity_recieved select. The problem is that if the quantity has two values that are the same such as 50, 50, 14, 25. The result will be 50, 14, 25. I just want to get rid of the repeating numbers and only get the values once. </p> <p>Here is the query:</p> <pre><code>SELECT `product`.`id`,`product`.`sku`,`product`.`name`, case when coalesce(stock1.`quantity`, '') = '' then '0' else stock1.`quantity` end as qty_warehouse, case when coalesce(sum(distinct stock2.`quantity`), '') = '' then '0' else sum(distinct stock2.`quantity`) end as qty_events, case when coalesce(stock1.`quantity`, '') = '' then '0' else stock1.`quantity` end + case when coalesce(sum(distinct stock2.`quantity`), '') = '' then '0' else sum(distinct stock2.`quantity`) end as qty_total GROUP_CONCAT(DISTINCT quantity_received) , GROUP_CONCAT(DISTINCT item_cost) FROM (`product`) LEFT JOIN`shipping_event` ON `shipping_event`.`product_id` = `product`.`id` LEFT JOIN `product_stock` as stock1 ON `product`.`id` = `stock1`.`product_id` and `stock1`.`location_id` = 112 LEFT JOIN `product_stock` as stock2 ON `product`.`id` = `stock2`.`product_id` and `stock2`.`location_id` != 112 LEFT JOIN `shipping_list` ON `shipping_event`.`shipping_list_id` = `shipping_list`.`id` WHERE `shipping_list`.`type` = 'incoming' AND `shipping_event`.`end_date` &gt; '2004-01-01 01:01:01' GROUP BY `product`.`id` ORDER BY `sku` asc LIMIT 20 </code></pre> <p>I am using group concat just to display the result in this case. I actually sum the quantity_received and then multiple them by the item cost. </p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USShegit Brahm
    1. USTom
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POLeft Joins returns duplicate values
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POLeft Joins returns duplicate values
      1. This table or related slice is empty.
      1. VTApproveEditSuggestion
    3. VO
      singulars
      1. POLeft Joins returns duplicate values
      1. This table or related slice is empty.
      1. VTUpMod
    1. COhave you tried to use DISTINCT in your select part?
      singulars
      1. POLeft Joins returns duplicate values
      1. USHajo
    2. COYes, but like I explained that with DISTINCT , if the quantity_received has two values that are the same such as 50, 50, 14, 25. The result will be 50, 14, 25. In the example I need to get the four distinct values before they repeat.
      singulars
      1. POLeft Joins returns duplicate values
      1. USTom
    3. COWhich table do the `quantity_received` and `item_cost` come from?
      singulars
      1. POLeft Joins returns duplicate values
      1. USypercubeᵀᴹ
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload