surya teja surya teja - 8 days ago 5
SQL Question

SQL query help in where condition

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

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;