Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

Check that a specific Group By condition does not exist Expand / Collapse
Author
Message
Posted Friday, September 07, 2012 9:09 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, March 28, 2014 2:40 PM
Points: 179, Visits: 382
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.
Post #1356051
Posted Friday, September 07, 2012 9:15 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, April 17, 2014 9:33 AM
Points: 6,754, Visits: 12,854
SELECT b.* 
FROM #tblBlocks b
WHERE NOT EXISTS (
SELECT 1
FROM #tblBlocks bi
WHERE bi.BlockID = b.BlockID
AND bi.BaseStage = 1)



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
Post #1356057
Posted Friday, September 07, 2012 9:24 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 11:40 AM
Points: 22,509, Visits: 30,227
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





Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1356061
Posted Friday, September 07, 2012 10:09 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, March 28, 2014 2:40 PM
Points: 179, Visits: 382
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.
Post #1356086
Posted Friday, September 07, 2012 10:16 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 11:40 AM
Points: 22,509, Visits: 30,227
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1356089
Posted Friday, September 07, 2012 10:18 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, March 28, 2014 2:40 PM
Points: 179, Visits: 382
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.
Post #1356090
Posted Friday, September 07, 2012 10:18 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 11:40 AM
Points: 22,509, Visits: 30,227
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





Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1356092
Posted Friday, September 07, 2012 10:21 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 11:40 AM
Points: 22,509, Visits: 30,227
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1356095
Posted Friday, September 07, 2012 11:29 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, March 28, 2014 2:40 PM
Points: 179, Visits: 382
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.
Post #1356137
Posted Friday, September 07, 2012 11:37 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 4:53 PM
Points: 1,746, Visits: 2,553
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)
I'm not fat, I'm gravity challenged.
Post #1356142
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse