Help with a subquery

Databases
1

What I want returned is…

SELECT
0_item_codes.stock_id
, 0_item_codes.description

FROM
0_item_codes

But to get what I need I need that select statement to use the result from a subqury…
SELECT
stock_id
, SUM( qty )

FROM
0_stock_moves

GROUP BY
stock_id

Where the sum for the stock_id is > 0_loc_stock.reorder_level
and I also need to put in this where cluse…
WHERE
0_item_codes.inactive =0
AND 0_item_codes.category_id =7

I just can not seem to put all these peices together…

thanks for the help

AM

2

You only need to SUM (0_stock_moves.qty) to get the current quantity for that stock item, and for that location. 0_loc_stock.reorder_level is just a static number for a stock item and a location. I need to know what stock items, current quantity, for that location is less then the reorder level for those stock items and location.

3

Beats me why you’d want to confront a quantity with a ‘reorder level’, whatever a ‘reorder level’ is :slight_smile:

But, the group by will give you just one ‘reorder level’ from all those present with the given stock id and loc_code = 20. Maybe you should SUM the 'reorder level’s as well?

4

I don’t think we have one for this table. I did solve the problem be inserting the ( GROUP BY stock_id) clause. I am not sure this is the right way? Also to get what I what I had to add the ( 0_loc_stock.loc_code = 20) in two places. So looks like I could use a little restructuring?

The table you ask about: (0_loc_stock)
Field | Type |Collation
loc_code |char(5) |latin1_swedish_ci
stock_id |char(20) |latin1_swedish_ci
reorder_level |bigint(20) |
reorder_qty |bigint(20) |

The code I have so far:

SELECT
     0_item_codes.stock_id
,    0_item_codes.description
,      SUM(0_stock_moves.qty)
FROM
    0_item_codes
  JOIN 0_stock_moves
    ON 0_item_codes.stock_id = 0_stock_moves.stock_id
WHERE 
    0_item_codes.inactive = 0
   AND 0_item_codes.category_id = 7
   AND 0_stock_moves.loc_code = 20
GROUP BY 
    0_item_codes.stock_id
  HAVING SUM(0_stock_moves.qty) > 
 (    SELECT reorder_level
         FROM
	 0_loc_stock
        WHERE
        0_loc_stock.loc_code = 20
	AND 0_loc_stock.stock_id = 0_item_codes.stock_id
	 GROUP BY 
		stock_id

)
5

If the value of both loc_codes is the same, I think you can change the line in the subquery in:

0_loc_stock.loc_code = 0_stock_moves.loc_code

6

Thanks for your help, I had to add the loc_code = 20 to both where clauses to get the right result. So as you pointed out, and I now know the group by in subquery is not needed.

But my last question was about restructuring the statement so as to have the loc_code =X stated only one time in the statement.

If that is possible??

7

That’s ok :slight_smile:
Recommendations? None, except that there’s no need for the group by in the subquery as far as I can see. Try it without. For the rest it looks good to me :slight_smile:

8 

select 0_item_codes.stock_id, 
       0_item_codes.description,
       sum(0_stock_moves.qty)
  from 0_item_codes
  join 0_stock_moves
    on 0_item_codes.stock_id = 0_stock_moves.stock_id
 where 0_item_codes.inactive = 0
   and 0_item_codes.category_id = 7
 group by 0_item_codes.stock_id
having sum(0_stock_moves.qty) > 
      (select reorder_level
         from 0_loc_stock
        where 0_loc_stock.stock_id = 0_item_codes.stock_id)

You may have to change some column name.

9

Welcome to the SP forums.

Could you please describe in your own words what it is you want to achieve (without using SQL language), and also add a description of the tables?

10

I need a report to use for inventory management. So I set out to learn some SQL. But it will take some time.

This report will tell me what items for that location I need to reorder. The 0_stock_moves table keeps track of item movements. every row in this table is a stock or item movement, the qty column is a negative or positive number. When you add all the qty numbers for that item code you get the current inventory quantity for that item code (stock_id). I need to filter that by location code equals (0_stock_moves.loc_code =X) Then I need to take this total for that stock_id and compare it to the 0_loc_stock table, reorder_level column for current inventory level that is less then reorder level for all stock_id for that location (0_loc_stock.loc_code =X) And then sometime filter that by the category code. I do see a mistake here, I need to go to the table 0_stock_master column category_id to get the category code, And I need filter out inactive items, this column is in two tables 0_item_codes.inactive and 0_stock_master.inactive (inactive =0) I am not sure about table descriptions, but stock_id is the common column.

Please let me know, thanks

11

A while back I asked you for the unique key, and you said there wasn’t. And now there is: loc_code and stock_id.

So there’s no need for group by. As long as you select one specific loc_code (20), you’ll always get only 1 row in return.

12

OK, sorry about that, comes with being a newbe, and trying to understand how all this works.

Thanks very much for you time…:slight_smile:

What recommendation to my statement can you make?

13

In table 0_loc_stock the rows are unique by loc_code and stock_id

300×300 14.3 KB

In table 0_stock_moves rows are unique by transaction.
300×300 14.3 KB

14

This subquery gives the error Unknown column ‘x_xx.0_stock_moves.loc_code’ in ‘where clause’

(    SELECT reorder_level
         FROM
	 0_loc_stock
        WHERE
     0_loc_stock.loc_code = 0_stock_moves.loc_code
	 
)
15

Hmmm, no idea why. Let’s wait for some other opinions (rudy?)…

16

Ok. And for each stock item/location you can have more than 1 reorder level? Which one do you need? The max? The min?

17

Ok. But you said that for one stock item id you can have more than 1 row in that table, that’s why you introduced the group by. Right?
I’m asking you if there is a certain logic in these multiple rows? And if there is a certain one of these rows that you want to use in case there are more than one. Or does it make no difference, any of them suits you fine?

18

This is getting close, but I get the error (Subquery returns more than 1 row)

19

The reorder level can be changed to a higher number if you have an item that sells so fast you have a hard time keeping it in stock. When your inventory level drops below the reorder level it would flag for a reorder…

20

You have multiple lines for the same stock_id in the 0_loc_stock table? What is the unique key for that table?