Receive All Entries (even 'duplicates' in MySQL Select Statement

Question

Here is my MySQL Query:

$query = "SELECT
  ws_inventory.id,
  ws_inventory.SKU AS inventory_sku,
  ws_inventory.Quantity AS inventory_quantity,
  ws_mastersku.SKU_1,
  ws_mastersku.SKU_2,
  ws_mastersku.SKU_4,
  ws_mastersku.SKU_3,
  ws_mastersku.MultSKU,
  ws_mastersku.QtySKU,
  ws_mastersku.AltSKU,
  ws_mastersku.SKU,
  ws_sold.SKU AS sold_sku,
  ws_sold.Quantity AS sold_quantity
FROM ws_sold
   LEFT OUTER JOIN ws_mastersku
    ON ws_sold.sku = ws_mastersku.SKU
   LEFT OUTER JOIN ws_inventory
    ON ws_mastersku.SKU_1 = ws_inventory.SKU
    OR ws_mastersku.AltSKU = ws_inventory.SKU
GROUP BY  ws_sold.sku;";

But it's only returning the first instance of WS16

Even though my table has two entries for WS16

How can I modify my Query so that it will select ALL the rows? (in my last picture it is omitting row id #4)

The new select query should look like (which as you can see the fourth column here)

1   WS16    91  (null)  (null)  (null)  (null)  0   4   WS16    WS16X4-2    WS16X4-2    2
2   WS3     97  (null)  (null)  (null)  (null)  0   2   WS3     WS3X2-4 WS3X2-4 1
3   WS6     95  (null)  (null)  (null)  (null)  0   4   WS6     WS6X4-16    WS6X4-16       1
4   WS16    75  (null)  (null)  (null)  (null)  0   4   WS16    WS16X4-2    WS16X4-2    4

Per Keith and Tim's kind responses, I've tried the query without the GROUP BY clause and here is my result

It is a step in the right direction but the inventory_quantity is wrong..

This is because in my PHP script I am doing

$newQuantity =  $rs['inventory_quantity'] - ($rs['sold_quantity'] * $rs['QtySKU']);

And the output of the script in my while loop is

UPDATE ws_inventory SET Quantity = 91 WHERE SKU = 'WS16'

followed by

UPDATE ws_inventory SET Quantity = 83 WHERE SKU = 'WS16'

Issue is because inventory_quantity is taken at the start the query. Quantity for first statement is -8 (start 99), and second statement is -16 (start 99). Need to know figure out a way to get it so that statements will work with eachother in this case, and do a 99 - 8 - 16 = 75 instead of just overwriting the result..

What output do you actually want? Your current query has a problem with the way you are using `GROUP BY`. — Tim Biegeleisen, Nov 21 '16 at 05:16
No...update your question and show us the output you want. Keith's suggestion below to drop `GROUP BY` entirely may be correct, unless you have a different expectation. — Tim Biegeleisen, Nov 21 '16 at 05:22
Now that I think of it, the query should not do anything special with quantity. I am using this SELECT statement in conjunction with a PHP script which does that math for me. The logic behind this is `$rs['inventory_quantity'] - ($rs['sold_quantity'] * $rs['QtySKU']);` but I think this is irrelevant as I am just having issues with the query. — bbruman, Nov 21 '16 at 05:28
Okay I just made this comment and now I'm thinking that this is not relevant, as I might be able to figure out a way to fix the logic in PHP... but if there is a way to do this in MySQL? I'm thinking if I could just get it down in the Query it'd be much easier — bbruman, Nov 21 '16 at 05:49
My apologies. My first statement is `UPDATE ws_inventory SET Quantity [99-8] = 91 WHERE SKU = 'WS16'` and my second statement is `UPDATE ws_inventory SET Quantity [99-16] = 83 WHERE SKU = 'WS16'`. So the end result is just my last statement and the first statement is being made irrelevant by overwriting it. My second statement needs to reflect the first statements change and result in `UPDATE ws_inventory SET Quantity [91-16] = 75 WHERE SKU = 'WS16'`. Hope this is clear. — bbruman, Nov 21 '16 at 06:09
Looks like you want a running total which is an entirely different question. If you do want a running total please tidy this one up, close it off, and make a new question — Keith John Hutchison, Nov 21 '16 at 08:42
I just did, yes : http://stackoverflow.com/questions/40732946/keeping-a-running-total-of-inventory-quantity-in-mysql-database-query-operation — bbruman, Nov 22 '16 at 02:42

score 0 · Accepted Answer · answered Nov 21 '16 at 05:17

0

Try dropping the group by clause. You have no group by aggregate functions so there is no point in having the group by clause.

See https://www.google.com.au/webhp?sourceid=chrome-instant&ion=1&espv=2&ie=UTF-8#q=group%20by%20aggregate%20functions for more details on group by functions.

answered Nov 21 '16 at 05:17

Keith John Hutchison

4,955
11
46
64

I would have upvoted this if you had posted a direct link to the mysql page with a quote from it. – e4c5 Nov 21 '16 at 05:25
Thank you, this helped. I must have left that on there when doing previous testing. I've updated my question with the results of this. – bbruman Nov 21 '16 at 05:45
The answer does actually the question title "Receive All Entries (even 'duplicates' in MySQL Select Statement". Group by is causing the results to be ... grouped! – Keith John Hutchison Nov 21 '16 at 08:33

Receive All Entries (even 'duplicates' in MySQL Select Statement

1 Answers1