September 7, 2012 at 9:09 am
CREATE TABLE #tblBlocks
(
TemplateID int,
BlockID int,
FieldID int,
BaseStage bit
)
GO
INSERT INTO #tblBlocks (TemplateID, BlockID, FieldID, BaseStage)
SELECT 1, 1, 1, 0 UNION ALL
SELECT 1, 1, 2, 0 UNION ALL
SELECT 1, 1, 3, 0 UNION ALL
SELECT 1, 2, 4, 0 UNION ALL
SELECT 1, 2, 5, 1 UNION ALL
SELECT 1, 2, 6, 0
GO
--SELECT * FROM #tblBlocks
DROP TABLE #tblBlocks
I need to determine if any group of records in the table above - Grouped by BlockID - does not contain a value of 1 (or true) in the BaseStage column.
So, to the question - does any group of records, grouped by BlockID, not contain the value 1 (or true) in the records in the above temporary table - the answer would be 'Yes, there is a group without a true BaseStage value.
How can I ascertain this? I've tried all manner of Group Bys and can't figure it out.
Thanks for any help.
September 7, 2012 at 9:15 am
SELECT b.*
FROM #tblBlocks b
WHERE NOT EXISTS (
SELECT 1
FROM #tblBlocks bi
WHERE bi.BlockID = b.BlockID
AND bi.BaseStage = 1)
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 7, 2012 at 9:25 am
Try this:
CREATE TABLE #tblBlocks
(
BlockID int,
FieldID int,
BaseStage bit
)
GO
INSERT INTO #tblBlocks (BlockID, FieldID, BaseStage)
SELECT 1, 1, 0 UNION ALL
SELECT 1, 2, 0 UNION ALL
SELECT 1, 3, 0 UNION ALL
SELECT 2, 4, 0 UNION ALL
SELECT 2, 5, 1 UNION ALL
SELECT 2, 6, 0
GO
SELECT
*
FROM
#tblBlocks tb1
WHERE
NOT EXISTS(SELECT
1
FROM
#tblBlocks tb2
WHERE
tb2.BlockID = tb1.BlockID AND
tb2.BaseStage = 1);
go
--SELECT * FROM #tblBlocks
DROP TABLE #tblBlocks
go
September 7, 2012 at 10:09 am
Thanks for your reply ... it's giving me the opposite of what I want because I phrased my question badly.
I have modified my temporary table in my first post to include a TemplateID
Join to the temporary table but only if every group within the temporary table has a BaseStage of 1.
i.e. make it so that if there is a group without a BaseStage of 1 - it returns nothing - no join happens.
September 7, 2012 at 10:16 am
sku370870 (9/7/2012)
Thanks for your reply ... it's giving me the opposite of what I want because I phrased my question back to front.What I need is:
Join to the temporary table but only if every group within the temporary table has a BaseStage of 1.
i.e. make it so that if there is a group without a BaseStage of 1 - it returns nothing - no join happens.
Not sure what you are asking. You only provided one table in your initial post. Also, if I read this correction correctly, based on the data provided in the initial post, you want nothing back as none of the groups have a BaseStage = 1 for all rows in a group.
September 7, 2012 at 10:18 am
Apologies, I was modifying my first post when I thought - that will just confuse the issue. Can we start again please?
CREATE TABLE #tblBlocks
(
TemplateID int,
BlockID int,
FieldID int,
BaseStage bit
)
GO
INSERT INTO #tblBlocks (TemplateID, BlockID, FieldID, BaseStage)
SELECT 1, 1, 1, 0 UNION ALL
SELECT 1, 1, 2, 0 UNION ALL
SELECT 1, 1, 3, 0 UNION ALL
SELECT 1, 2, 4, 0 UNION ALL
SELECT 1, 2, 5, 1 UNION ALL
SELECT 1, 2, 6, 0
GO
--SELECT * FROM #tblBlocks
DROP TABLE #tblBlocks
If I join to the Table above on TemplateID - I need it to return nothing if any BlockID group does not contain a BaseStage of 1.
Sorry for screwing up.
September 7, 2012 at 10:18 am
Again, based solely on the initial post with a little more data added:
CREATE TABLE #tblBlocks
(
BlockID int,
FieldID int,
BaseStage bit
)
GO
INSERT INTO #tblBlocks (BlockID, FieldID, BaseStage)
SELECT 1, 1, 0 UNION ALL
SELECT 1, 2, 0 UNION ALL
SELECT 1, 3, 0 UNION ALL
SELECT 2, 4, 0 UNION ALL
SELECT 2, 5, 1 UNION ALL
SELECT 2, 6, 0 UNION ALL
SELECT 3, 7, 1 UNION ALL
SELECT 3, 8, 1 UNION ALL
SELECT 3, 9, 1
GO
SELECT
*
FROM
#tblBlocks tb1
WHERE
NOT EXISTS(SELECT
1
FROM
#tblBlocks tb2
WHERE
tb2.BlockID = tb1.BlockID AND
tb2.BaseStage = 0);
go
--SELECT * FROM #tblBlocks
DROP TABLE #tblBlocks
go
September 7, 2012 at 10:21 am
sku370870 (9/7/2012)
Apologies, I was modifying my first post when I thought - that will just confuse the issue. Can we start again please?
CREATE TABLE #tblBlocks
(
TemplateID int,
BlockID int,
FieldID int,
BaseStage bit
)
GO
INSERT INTO #tblBlocks (TemplateID, BlockID, FieldID, BaseStage)
SELECT 1, 1, 1, 0 UNION ALL
SELECT 1, 1, 2, 0 UNION ALL
SELECT 1, 1, 3, 0 UNION ALL
SELECT 1, 2, 4, 0 UNION ALL
SELECT 1, 2, 5, 1 UNION ALL
SELECT 1, 2, 6, 0
GO
--SELECT * FROM #tblBlocks
DROP TABLE #tblBlocks
If I join to the Table above on TemplateID - I need it to return nothing if any BlockID group does not contain a BaseStage of 1.
Sorry for screwing up.
All you did is add another column to the original table. You really haven't cleared anything up at all. Show us what the expected results should be. I can't figure out what you are trying to accomplish from what you have posted so far.
September 7, 2012 at 11:29 am
CREATE TABLE #tblTemplates
(
TemplateID int,
TemplateName varchar(50)
)
GO
INSERT INTO #tblTemplates (TemplateID, TemplateName)
SELECT 1, 'Template 1' UNION ALL
SELECT 2, 'Template 2'
GO
CREATE TABLE #tblBlocks
(
TemplateID int,
BlockID int,
FieldID int,
BaseStage bit
)
GO
INSERT INTO #tblBlocks (TemplateID, BlockID, FieldID, BaseStage)
SELECT 1, 1, 1, 0 UNION ALL
SELECT 1, 1, 2, 0 UNION ALL
SELECT 1, 1, 3, 0 UNION ALL
SELECT 1, 2, 4, 0 UNION ALL
SELECT 1, 2, 5, 1 UNION ALL
SELECT 1, 2, 6, 0 UNION ALL
SELECT 2, 1, 7, 1 UNION ALL
SELECT 2, 1, 8, 0 UNION ALL
SELECT 2, 1, 9, 0 UNION ALL
SELECT 2, 2, 10, 1 UNION ALL
SELECT 2, 2, 11, 0 UNION ALL
SELECT 2, 2, 12, 0
GO
SELECT Distinct #tblTemplates.TemplateName FROM #tblTemplates
INNER JOIN #tblBlocks ON #tblTemplates.TemplateID = #tblBlocks.TemplateID -- plus some more code to satisfy the condition below
DROP TABLE #tblBlocks
DROP TABLE #tblTemplates
A Templates table with two templates in it.
A Blocks table with BlockIDs in it for each template.
Each template has 6 BlockID records in #tblBlocks - in two groups of 3. There are 3 BlockID records with a value of 1 and 3 with a value of 2 for each template.
When I run the statement SELECT * FROM #tblTemplates ... I only want Templates returned that, in the Blocks table, each group of BlockIDs has at least one record with a value of 1 for BaseStage.
So, if I run the Select statement to return a list of Templates using the data above, I only want to see Template 2 returned. Template 1 does not qualify because it has a group of Block IDs none of which have a value of 1 for BaseStage.
Thanks again for your help.
September 7, 2012 at 11:37 am
SELECT BlockID
FROM #tblBlocks
GROUP BY BlockID
HAVING MAX(CASE WHEN BaseStage = 1 THEN 1 ELSE 0 END) = 0
Include the TemplateID in the SELECT and GROUP BY if required.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 7, 2012 at 11:54 am
SELECT Distinct #tblTemplates.TemplateName FROM #tblTemplates
INNER JOIN #tblBlocks ON #tblTemplates.TemplateID = #tblBlocks.TemplateID
INNER JOIN (SELECT TemplateID, BlockID
FROM #tblBlocks
GROUP BY TemplateID, BlockID
HAVING MAX(CASE WHEN #tblBlocks.BaseStage = 1 THEN 1 ELSE 0 END) = 0) B ON #tblBlocks.TemplateID = B.TemplateID
The above is returning me Template 1 - but I need the opposite. Template 2 has a BaseStage of 1 set for both groups - so this is okay. Template 1 has no BaseStage of 1 for the first group - so I don't want this returned.
Thanks for you help.
September 7, 2012 at 12:06 pm
sku370870 (9/7/2012)
SELECT Distinct #tblTemplates.TemplateName FROM #tblTemplates
INNER JOIN #tblBlocks ON #tblTemplates.TemplateID = #tblBlocks.TemplateID
INNER JOIN (SELECT TemplateID, BlockID
FROM #tblBlocks
GROUP BY TemplateID, BlockID
HAVING MAX(CASE WHEN #tblBlocks.BaseStage = 1 THEN 1 ELSE 0 END) = 0) B ON #tblBlocks.TemplateID = B.TemplateID
The above is returning me Template 1 - but I need the opposite. Template 2 has a BaseStage of 1 set for both groups - so this is okay. Template 1 has no BaseStage of 1 for the first group - so I don't want this returned.
Thanks for you help.
Sorry. To check for those including a BackStage of 1, just change the final comparison value:
HAVING MAX(CASE WHEN #tblBlocks.BaseStage = 1 THEN 1 ELSE 0 END) = 1
The key thing is that HAVING with CASE can help you in situations like this!
I based my first answer on the original q :-), viz:
I need to determine if any group of records in the table above - Grouped by BlockID - does not contain a value of 1 (or true) in the BaseStage column.
So, to the question - does any group of records, grouped by BlockID, not contain the value 1 (or true) in the records in the above temporary table - the answer would be 'Yes, there is a group without a true BaseStage value.
How can I ascertain this? I've tried all manner of Group Bys and can't figure it out.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 7, 2012 at 12:14 pm
Since all you want is the TemplateName where BaseStage is 1 in at least 1 record in a group, the following works:
CREATE TABLE #tblTemplates
(
TemplateID int,
TemplateName varchar(50)
)
GO
INSERT INTO #tblTemplates (TemplateID, TemplateName)
SELECT 1, 'Template 1' UNION ALL
SELECT 2, 'Template 2'
GO
CREATE TABLE #tblBlocks
(
TemplateID int,
BlockID int,
FieldID int,
BaseStage bit
)
GO
INSERT INTO #tblBlocks (TemplateID, BlockID, FieldID, BaseStage)
SELECT 1, 1, 1, 0 UNION ALL
SELECT 1, 1, 2, 0 UNION ALL
SELECT 1, 1, 3, 0 UNION ALL
SELECT 1, 2, 4, 0 UNION ALL
SELECT 1, 2, 5, 1 UNION ALL
SELECT 1, 2, 6, 0 UNION ALL
SELECT 2, 1, 7, 1 UNION ALL
SELECT 2, 1, 8, 0 UNION ALL
SELECT 2, 1, 9, 0 UNION ALL
SELECT 2, 2, 10, 1 UNION ALL
SELECT 2, 2, 11, 0 UNION ALL
SELECT 2, 2, 12, 0
GO
SELECT
*
FROM
#tblTemplates t1
WHERE
EXISTS (SELECT 1 FROM #tblBlocks tb1 WHERE tb1.TemplateID = t1.TemplateID AND tb1.BaseStage = 1);
go
DROP TABLE #tblBlocks
DROP TABLE #tblTemplates
GO
September 7, 2012 at 12:15 pm
Hi, I've modified the code:
SELECT Distinct #tblTemplates.TemplateName
FROM #tblTemplates
INNER JOIN #tblBlocks ON #tblTemplates.TemplateID = #tblBlocks.TemplateID
INNER JOIN (SELECT TemplateID, BlockID
FROM #tblBlocks
GROUP BY TemplateID, BlockID
HAVING MAX(CASE WHEN #tblBlocks.BaseStage = 1 THEN 1 ELSE 0 END) = 1) B ON #tblBlocks.TemplateID = B.TemplateID
but when I run that, it's returning both Templates - instead of just Template 2.
Cheers
September 7, 2012 at 12:18 pm
So what you are saying is that BaseStage must be 1 in at least one record of BOTH groups, correct?
Viewing 15 posts - 1 through 15 (of 30 total)
You must be logged in to reply to this topic. Login to reply