help with sql query to return existing and non existent records

Question

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

Answer 1

;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'))

Answer 2

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 idstyl

Answer 3

;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

Answer 4

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.

Answer 5

Hi @Gary Keramidas

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.