Click here to Skip to main content
15,868,099 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
how can I edit the quey below by writing the third condition to limit rows output ? Third condition is: -- an order not holding parts that are not gizmo or gadget. Another words: There are three different orderid values:

1. those with gadgets -----I am getting it fine in the query below.

2. AND those with gizmos --- I am getting it fine in the query below.

3. AND those not with not gadget or gizmos. --- I need to get this one as well and add to the subquery below.

I know that I need to query the INVENTORY table for all partids and descriptions that are NOT gadget and are NOT gizmo and need to get the orderids that do NOT contain those parts.

That is what I am looking for to add it to the quey below.

SQL
select oi.orderid as 'order id'
from orderitems as oi
where oi.partid in (select inv.partid
                    from inventory as inv
                    where inv.description like '%gizmo%'
                       or inv.description like '%gadget%'
                           )
group by oi.orderid
having count (oi.orderid)=2 -- tried to select only rows with only two partid
Posted
Updated 20-Jan-13 15:52pm
v2
Comments
7prince 20-Jan-13 21:07pm    
what about including the INVENTORY table for all partids and descriptions that are NOT gadget and are NOT gizmo and need to get the orderids that do NOT contain those parts. Basicall query should give out 3 conditions:

1. those with gadgets
2. AND those with gizmos
3. AND those not with not gadget or gizmos. --- I need to get this one as well and add to the subquery below.
_Maxxx_ 20-Jan-13 22:32pm    
Surely Gadgets and Gizmos and Neither gives you all orderitems - how can they be anything else?
Christian Graus 20-Jan-13 22:40pm    
Yes, that's my question too. But, I've provided the answer, and he seems to be giving me other requirements now

SQL
select oi.orderid as 'order id'

 from orderitems as oi

 where oi.partid in (select inv.partid

 from inventory as inv

 where inv.description like '%gizmo%'

 or inv.description like '%gadget%'

or (inv.description NOT LIKE '%gizmo%'

 and inv.description NOT like '%gadget%')

 )

 group by oi.orderid

 having count (oi.orderid)=2 -- tried to select only rows with only two partid


Of course, the only think this does not select, is stuff that is both a gizmo and a gadget.
 
Share this answer
 
v2
Comments
7prince 20-Jan-13 20:46pm    
yes. your query gives 2 rows. Should give one.
Christian Graus 20-Jan-13 20:51pm    
There is nothing in there to limit the number of rows, except the having statement, which I did not modify and assumed to work. I answered the bit you asked about
7prince 20-Jan-13 22:38pm    
basicall the query should include:

An order (orderid) is to contain gadgets and gizmos and nothing else.

At a minimum, then, an order would have one gadget and one gizmo, period.

But an order could contain any number of different kinds of gadgets AND any number of different kinds of gizmos as long as it had NO OTHER PARTS. Meaning, an orderid can have gadget or gizmo and the same orderid can have many other descriptions. I need only the orderids that has only gadget and gismo and those orderids not tied to any other description.
Christian Graus 20-Jan-13 22:40pm    
Sounds like it might be a union that you want then.
7prince 20-Jan-13 22:45pm    
that would be fine. or a subquery. it doesn't matter. Thanks.
I think you can siomplify this (If I am reading your requirements correctly)

You want only those orders where all order items are either Gadgets or Gizmos.

So

Select * from Order O
Where Not Exists

(
Select 1 
from OrderItems OI
join Inventory I on OI.InventoriId = I.InventoryId
where O.OrderId = OI.OrderId
    and I.Description Not like '%gizmo%'
    and I.Description Not like '%gadget%'
)
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900