SQL query help in where condition

Question

I am working on a report query which need to display orders contain both p type items and other non p items(non P items are of 50 items), they want order details that combine both type of items

Below is the query which I have prepared, but this query also displaying ptype orders which are not combined with non p items.

SELECT
  vwOrD.ONUMBER,
  vwOrD.ITEMID,
  vwITEMs.cat,
  vwITEMs.id
FROM vwITEMs
INNER JOIN vwOrD
  ON vwITEMs.ITEMID = vwOrD.ITEMID
INNER JOIN vwOrders
  ON vwOrD.ONUMBER = vwOrders.ONUMBER
WHERE vwOrders.CUSTID        = 'test'
  AND vwOrders.CREATEDATE >= '1-1-2016'
  AND vwOrders.CREATEDATE <= '11-28-2016'
  AND vwOrD.ONUMBER       IN
  (SELECT vwOrD.ONUMBER
   FROM vwOrD
   INNER JOIN vworders
    ON vwOrD.ONUMBER = vwOrders.ONUMBER
   INNER JOIN vwITEMs
    ON vwITEMs.ASCITEMID = vwOrD.ASCITEMID
    WHERE vwOrders.SOLDTOCUSTID  = 'test'
      AND vwITEMs.cat          = N'PI' -- Pitems cat= pi, id = c
      AND vwITEMs.id           = 'C'
      AND vwOrders.CREATEDATE >= '1-1-2016'
      AND vwOrders.CREATEDATE <= '11-28-2016' --group by          vwOrD.ONUMBER
      -- having count(1) > 1
  )
ORDER BY
  vwOrD.ONUMBER

sample output generated:

  ornumber  idnum  categ id  id
        12    xxx        pi   c 
        12    xxx     nonpi   c
        11    yyy        pi   c
        10    qqq        pi   c

results expected

12 xxx    pi c
12 xxx nonpi c

Answer 1

I'm not sure why the columns in your sub-query are different, but I don't think that's the root of your trouble.

You are using your subquery to make sure that for each row you are returning, there is an item in that order with a 'pi' item. That's a little different from what you said you were trying to do.

The query below returns rows that are 'pi' or 'nonpi' that have another row for that onumber that is also 'pi' or 'nonpi' but is not the same cat as the row it is checking against.

select 
    d.onumber 
  , d.itemid
  , i.cat
  , i.id 
  from vwitems as i
    inner join vwOrD    as d on i.itemid = d.itemid 
    inner join vwOrders as o on d.onumber = o.onumber 
  where o.custid = 'test' 
    and o.createdate >= '1-1-2016' 
    and o.createdate <= '11-28-2016' 
    and exists (
      select 1 
        from vwOrD
          inner join vwitems on vwitems.ascitemid = vwOrD.ascitemid  /* ascitemid vs itemid ? */
          and vwitems.cat = 'Pi'
          and vwitems.id = 'C' 
          and vwOrD.onumber=o.onumber
          )
    and exists (
      select 1 
        from vwOrD
          inner join vwitems on vwitems.ascitemid = vwOrD.ascitemid  /* ascitemid vs itemid ? */
          and vwitems.cat != 'Pi'
          and vwitems.id = 'C' 
          and vwOrD.onumber=o.onumber
          )
  order by d.onumber;