help with sql query to return existing and non existent records

Gary Keramidas 41 Reputation points
2023-03-06T19:23:30.2166667+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,851 questions
0 comments No comments
{count} votes

6 answers

Sort by: Most helpful
  1. Jingyang Li 5,891 Reputation points
    2023-03-06T20:07:50.2166667+00:00

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

    0 comments No comments

  2. Gary Keramidas 41 Reputation points
    2023-03-06T22:19:14+00:00

    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

    0 comments No comments

  3. Jingyang Li 5,891 Reputation points
    2023-03-06T22:29:42.5066667+00:00

    ;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

    0 comments No comments

  4. Gary Keramidas 41 Reputation points
    2023-03-07T01:03:27.3733333+00:00

    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.

    0 comments No comments

  5. CosmogHong-MSFT 23,556 Reputation points Microsoft Vendor
    2023-03-07T01:54:28.2166667+00:00

    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.

    0 comments No comments