;with mycte as (
select styl from (values('799941'), ('799950'),('799981')) d(styl)
)
select m.styl, isnull(val,0) val
from mycte m left join idcase i on i.styl=m.styl
where (i.whse = 'FP' and i.styl IN ('799941', '799950','799981'))
This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
i've tried a few example and can't seem to grasp how to do this. can someone help?
this query returns 1 row with the quantity for the item. there are 2 items not currently in inventory, so they're not in the table.
select styl from idcase where (whse = 'FP' and styl IN ('799941', '799950','799981'))
799941 200
how do i write the query so it shows
799941 200
799950 0
799981 0
;with mycte as (
select styl from (values('799941'), ('799950'),('799981')) d(styl)
)
select m.styl, isnull(val,0) val
from mycte m left join idcase i on i.styl=m.styl
where (i.whse = 'FP' and i.styl IN ('799941', '799950','799981'))
thanks for your response. somehow i pasted the wrong code in my original post, but all that was missing was the qty, which i explained. sorry for that.
unfortunately, the program i use to query came back with a couple errors.
it choked on the initial semi-colon
Token ; was not valid. Valid tokens: ( CL END GET SET TAG CALL DROP FREE HOLD LOCK OPEN WITH ALTER.
after i removed it, i got a val error
Column or global variable VAL not found.
here is code thsat returns a qty for 1 single item that has inventory
SELECT idstyl, Count(*) AS qty
FROM idcase05n
WHERE idwhse = 'FP' AND (idstyl IN ('799941', '799950', '799981') )
GROUP BY idsty
l
;with mycte as (
select idstyl from (values('799941'), ('799950'),('799981')) d(idstyl)
)
,yourquery as (
SELECT idstyl, Count(*) AS qty FROM idcase05n
WHERE idwhse = 'FP' AND (idstyl IN ('799941', '799950', '799981') )
GROUP BY idstyl
)
select m.idstyl, isnull(qty,0) qty
from mycte m
left join yourquery i on i.idstyl=m.idstyl
thanks again, but no luck. gives me a table not found error.
i would think this would be a very common task.
someone has 100 item numbers. they do a query against a table that has inventory quantities. 50 items are returned with quantities.
how do i know which 50 i need to order? i can write excel code to compare, but this should be possible in a query and EASILY DONE! whoever owns the sql language ought to just create a function within sql to automatically do this. or someone should sell something, they'd make a fortune.
help with sql query to return existing and non-existent records.
When you use WHERE clause to filter data, then of course you will never see non-existent records in the final output. Because this clause is the initial filtering of the data that was retrieved in the FROM clause. This is all about finding the rows that need to be considered in the query.
What you need is to add the filter condition inside the aggregate function using CASE WHEN.
Please try something like this:
create table #idcase05n(idstyl varchar(18),idwhse varchar(18))
insert into #idcase05n values
('799941','FP'),('799941','FP'),('799941','FP'),
('799950','DP'),('799950','GP'),
('799981','GP'),('799981','SP')
SELECT idstyl,SUM(CASE WHEN idwhse = 'FP' THEN 1 ELSE 0 END) AS qty
FROM #idcase05n
GROUP BY idstyl
Best regards,
Cosmog Hong
If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.