query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

query

Author

Topic

barnabeck
Posting Yak Master

236 Posts

Posted - 2015-01-22 : 07:10:55

I have productionorders in one table "ProdTable": 'WO15001, WO15002,..'
Each order can go through a series of different operations assigned to workcenters. These operations are defined in the table "ProdRoute"

ProdID    WorkCenter
--------------------
WO15001     1
WO15001     2
WO15001     3
WO15002     2
WO15002     4
WO15003     4
WO15004     ....

I need a query that returns only those WorkOrders that go ONLY through WorkCenter '4' and do NOT have any other Workcenters involved in their route.

In this example only WO15003 would be returned!

I came up with a solution which is rather lenghty and complicated (join/group/count/re-join) and was asking myself if there wasn't a smoother way to do it, taking maybe advantage of the cool features in SQL2012.

SELECT a.ProdID, a.CNT
FROM (SELECT ProdRoute.ProdID, COUNT(ProdRoute.WorkCenter) as CNT
      FROM ProdTable
           inner join ProdRoute on ProdTable.ProdID = ProdRoute.ProdID
      GROUP BY ProdRoute.ProdID)a
   inner join ProdRoute on a.ProdID = ProdRoute.ProdID and a.CNT = 1 and ProdRoute.WorkCenter = '4'

Any ideas?
Martin

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2015-01-22 : 13:36:56

This suggestion uses generic sql, which should work on most engines (no fancy 2012 stuff). It's just the way I would have written the query:

select a.ProdID
      ,count(*) as CNT
  from ProdTable as a
       inner join ProdRoute as b
               on b.ProdID=a.ProdID
 group by a.ProdID
 having count(*)=sum(case when b.WorkCenter='4' then 1 else 0 end)

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2015-01-22 : 13:38:22

[code]SELECT a.ProductId
FROM ProdTable a
WHERE NOT EXISTS
(
SELECT *
FROM ProdTable b
WHERE a.ProdId = b.ProdId
AND a.WorkCenter <> 4
);[/code]

barnabeck
Posting Yak Master

236 Posts

Posted - 2015-01-23 : 06:46:55

Thank you guys! Seeing how others solve an SQL Problem always makes you learn and get new inputs!
I take James' solution as it is the more compact and organic one.

Martin

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources