Query Help!

[ReportsMan]

I need to pull only Service Order Ids that are made up of entirely a certain type of product. Service Orders can contain many Products (1 ServiceOrder to Many LineItems) and I am looking for Service Order Ids that only contain ProductNames like "Update" and no other products.

Here are the tables:

->ServiceOrder
Fld1:ServiceOrderId

->LineItems
Fld1:ServiceOrderId
Fld2roductId

->Products
Fld1roductId
Fld2roductName


I hope thats clear enough -

Thanks for the help!

 

[wsmall73]

SELECT Distinct ServiceOrderId
FROM LineItems l (NOLOCK), Products p (NOLOCK)
WHERE l.productid=p.productid and
p.productname like '%update%'


?? I am not sure if this is what you are looking for??

 

[ReportsMan]

Thanks wsmall73 - but that gets me service order ids that also may contain other products, not just update products.

I just need service order ids that contain only update products and no other products.
Maybe this illustration will help:

ServiceOrderId ProductName
1 Website
1 Form
1 Update-Form
2 Update-Form
2 Update-Website

I only want to return service order id 2 - because it only has "Update" type products.

THANKs Again!

 

[tlbroadbent]

This kind of query is always difficult. Here is one solution.

Select o.ServiceOrderId, p.ProductName
From ServiceOrder o
Inner Join LineItems i
On o.ServiceOrderId=i.ServiceOrderId
Inner Join Products p
On i.ProductId=p.ProductId
Inner Join
(Select o.ServiceOrderId
From ServiceOrder o
Join LineItems i
On o.ServiceOrderId=i.ServiceOrderId
Join Products p
On i.ProductId=p.ProductId
Group By o.ServiceOrderId
Having Count(*)=Sum(
Case When charindex('update',p.ProductName)>0
Then 1 Else 0 End)) q
On o.ServiceOrderId=q.ServiceOrderId Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[ReportsMan]

Terry -

Muchos Gracias for the code! It really did the trick! Your use with the Having clause was excellent.

Thanks again!