SQL查询 - 通过向子查询添加第三个条件来限制行 [英] SQL query - Limiting rows by adding third condition to the subquery

问题描述

如何通过编写限制行输出的第三个条件来编辑下面的quey？第三个条件是： - 不保存非Gizmo或小工具的部件的订单。换句话说：有三种不同的orderid值：



1.带小工具的人-----我在下面的查询中做得很好。



2.和小玩意儿的人---我在下面的查询中得到了很好的结果。



3. AND那些没有小工具或小玩意儿的人。 ---我也需要得到这个，然后添加到下面的子查询中。



我知道我需要查询INVENTORY所有partid和描述的表格不是小工具，不是小发明，需要获取不包含这些部分的orderid。



这就是我想把它添加到下面的quey中。

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.

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

推荐答案

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

当然，唯一认为这不选择，就是这样的东西是一个Gizmo和一个小工具。

Of course, the only think this does not select, is stuff that is both a gizmo and a gadget.

我认为你可以简化这个（如果我正确地阅读你的要求）



您只需要所有订单商品为Gadgets或Gizmos的订单。



所以

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

这篇关于SQL查询 - 通过向子查询添加第三个条件来限制行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！