Query to select records only when all records in group meets the same criteria
-
Hi!
Here is my table data:
--Creating the tables
CREATE TABLE
#Machine (
MachinePk INT,
MachineTypeFk INT,
OnHand INT,
Enabled BIT
)
CREATE TABLE
#MachineBranch (
MachineBranchPk INT,
MachineFk INT,
BranchFk INT,
LastActivity DATE
)
--Populating the tables with data
INSERT INTO #Machine (MachinePk, MachineTypeFk, OnHand, Enabled) VALUES (1,99,0,1)
INSERT INTO #Machine (MachinePk, MachineTypeFk, OnHand, Enabled) VALUES (2,44,0,0)
INSERT INTO #Machine (MachinePk, MachineTypeFk, OnHand, Enabled) VALUES (3,99,0,0)
INSERT INTO #Machine (MachinePk, MachineTypeFk, OnHand, Enabled) VALUES (4,99,0,0)
INSERT INTO #Machine (MachinePk, MachineTypeFk, OnHand, Enabled) VALUES (5,99,135,0)
INSERT INTO #MachineBranch (MachineBranchPk, MachineFk, BranchFk, LastActivity) VALUES (1,1,27,'05/10/2013')
INSERT INTO #MachineBranch (MachineBranchPk, MachineFk, BranchFk, LastActivity) VALUES (2,1,31,'05/22/2013')
INSERT INTO #MachineBranch (MachineBranchPk, MachineFk, BranchFk, LastActivity) VALUES (3,2,12,'05/08/2013')
INSERT INTO #MachineBranch (MachineBranchPk, MachineFk, BranchFk, LastActivity) VALUES (4,2,75,'05/27/2013')
INSERT INTO #MachineBranch (MachineBranchPk, MachineFk, BranchFk, LastActivity) VALUES (5,3,52,'05/04/2013')
INSERT INTO #MachineBranch (MachineBranchPk, MachineFk, BranchFk, LastActivity) VALUES (6,3,25,'05/11/2013')
INSERT INTO #MachineBranch (MachineBranchPk, MachineFk, BranchFk, LastActivity) VALUES (7,3,18,'05/10/2013')
INSERT INTO #MachineBranch (MachineBranchPk, MachineFk, BranchFk, LastActivity) VALUES (8,4,16,'05/10/2013')
INSERT INTO #MachineBranch (MachineBranchPk, MachineFk, BranchFk, LastActivity) VALUES (9,4,22,'11/17/2014')
INSERT INTO #MachineBranch (MachineBranchPk, MachineFk, BranchFk, LastActivity) VALUES (9,5,11,'05/15/2013')
--List of machine/branch info with the proper filters
SELECT *
FROM #Machine m
JOIN #MachineBranch mb ON mb.MachineFk = m.MachinePk
WHERE m.MachineTypeFk = 99
AND m.OnHand = 0
AND m.Enabled = 0
AND mb.LastActivity < '06/01/2013'
--Record I am expecting back (since we only want the MachinePk in which every record in MachineBranch for that MachinePk meets the filter criteria)
SELECT MachinePk FROM #Machine WHERE MachinePk = 3
--Dropping the tables
DROP TABLE #Machine
DROP TABLE #MachineBranch
I need to write a select query that will retrieve any MachinePk in which:
1) Machine.MachineTypePk = 99
2) Machine.OnHand = 0
3) Machine.Enabled = 0
4) MachineBranch.LastActivity is older than 6/1/2013
In the example I've provided, I would only expect to retrieve the MachinePk of 3. This is because MachinePk #1, #2 and #5 don't pass the basic criteria for the Machine table, and MachinePk #4 only has on record in MachineBranch that passes the LastActivity criteria. All of that MachinePk's records in MachineBranch would need to pass the LastActivity criteria.
What would be the most efficient way to write such a query?
I greatly appreciate any assistance!
-
Try this
SELECT *
FROM #Machine m
WHERE m.MachineTypeFk = 99
AND m.OnHand = 0
AND m.Enabled = 0
AND EXISTS(SELECT * FROM #MachineBranch mb WHERE mb.MachineFk = m.MachinePk)
AND NOT EXISTS(SELECT * FROM #MachineBranch mb WHERE mb.MachineFk = m.MachinePk AND mb.LastActivity >= '06/01/2013')
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
I'll give this a try. Thanks Mark!
-
Here's another option.
SELECT *
FROM #Machine m
JOIN #MachineBranch mb ON mb.MachineFk = m.MachinePk
WHERE m.MachinePk IN ( SELECT im.MachinePk
FROM #Machine im
JOIN #MachineBranch imb ON imb.MachineFk = im.MachinePk
WHERE im.MachineTypeFk = 99
AND im.OnHand = 0
AND im.Enabled = 0
GROUP BY im.MachinePk
HAVING MAX( imb.LastActivity) < '06/01/2013')
-
Another way that might work for you, depending on what columns you need out of #MachineBranch:
SELECT a.MachineFK, b.*
FROM
(
SELECT MachineFK
FROM #MachineBranch
GROUP BY MachineFK
HAVING MAX(LastActivity) < '2013-06-01'
) a
JOIN #Machine b ON a.MachineFK = b.MachinePK
WHERE OnHand = 0 AND [Enabled] = 0 AND b.MachineTypeFK = 99;
My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St -
Tried all three of the proposed solutions. Each returned the same number of records and completed in less than a second, so I guess I could go with any of them.
Thank you Mark, Luis and Dwain for your help! Greatly appreciated.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply