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

Grouping by columns to create single record Expand / Collapse
Author
Message
Posted Thursday, August 1, 2013 11:05 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, December 11, 2013 1:38 PM
Points: 4, Visits: 12
I am in a time crunch, many things on my plate today, and I just can't seem to get this problem, thought I would toss it out to the forum, and someone would easily see the answer.
My Table looks like this:
AttribID FormatID
-------- ---------
12 34
15 34
19 34
25 34
12 49
15 49
27 49

I want to basically query the table like this: Give me FormatID when AttribID=12 AND AttribID=15 AND NOT AttribID = 27, and I want to get back one answer:34

I know it is a grouping issue, but I am not getting the exact syntax, and am running out of time. Can anyone see the obvious solution that I am missing

Thanks
Brian
Post #1480043
Posted Thursday, August 1, 2013 11:16 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, August 18, 2014 8:36 AM
Points: 2,836, Visits: 5,066
BFSTEP (8/1/2013)
I am in a time crunch, many things on my plate today, and I just can't seem to get this problem, thought I would toss it out to the forum, and someone would easily see the answer.
My Table looks like this:
AttribID FormatID
-------- ---------
12 34
15 34
19 34
25 34
12 49
15 49
27 49

I want to basically query the table like this: Give me FormatID when AttribID=12 AND AttribID=15 AND NOT AttribID = 27, and I want to get back one answer:34

I know it is a grouping issue, but I am not getting the exact syntax, and am running out of time. Can anyone see the obvious solution that I am missing

Thanks
Brian


It's not a grouping issue - it is a logical issue.
It's impossible for AttribID to be equal to 12 AND 15 at the same time, and if it is 12 or 15 that it cannot be 27, so no need to check this.

May be you need something like that:

SELECT DISTINCT FormatID  FROM [YourTable] WHERE AttribID IN (12,15)



_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1480047
Posted Thursday, August 1, 2013 11:21 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 10:16 AM
Points: 1,370, Visits: 1,545
BFSTEP (8/1/2013)
I am in a time crunch, many things on my plate today, and I just can't seem to get this problem, thought I would toss it out to the forum, and someone would easily see the answer.
My Table looks like this:
AttribID FormatID
-------- ---------
12 34
15 34
19 34
25 34
12 49
15 49
27 49

I want to basically query the table like this: Give me FormatID when AttribID=12 AND AttribID=15 AND NOT AttribID = 27, and I want to get back one answer:34

I know it is a grouping issue, but I am not getting the exact syntax, and am running out of time. Can anyone see the obvious solution that I am missing

Thanks
Brian


1. The AttribID will never be 12 AND 15...so do you mean 12 OR 15?
2. What about the row where Attrib is 12 and the answer would be 49...why exactly do you only want to have an answer of 34?
Post #1480049
Posted Thursday, August 1, 2013 11:30 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 3:34 PM
Points: 3,513, Visits: 7,563
You could find this article useful
http://www.sqlservercentral.com/articles/T-SQL/88244/

SELECT AttribID
FROM MyTable
WHERE FormatID IN (12,15)
GROUP BY AttribID
HAVING COUNT(DISTINCT FormatID ) = 2
EXCEPT
SELECT AttribID
FROM MyTable
WHERE FormatID <> 27




Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1480050
Posted Thursday, August 1, 2013 11:36 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, December 11, 2013 1:38 PM
Points: 4, Visits: 12
Let me put it to you this way: I have two Format IDs in the table, with four different Attriutes attached to them. For FormatID 34, it is attributes 12,15, 19, and 25. For FormatID 49, it is attributes 12, 15, and 27. I want to search so that I get all FormatIDs that have AttributeIDs of 12 AND 15 AND NOT 27. FormatID34 has 12, and 15 and not 27 so matches my desired query, FormatID 49 has 12, AND 15, AND 27 so does not match my desired query. That is what I meant by AttributeId=12 and AttributeID=15, and why I only want 34 as my answer. It is the FormatID that matches the critera of the query.

Thanks,
Brian S.
Post #1480054
Posted Thursday, August 1, 2013 12:26 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, December 11, 2013 1:38 PM
Points: 4, Visits: 12
Thanks, This actually does what I want, and would not be hard to construct, even within a Stored Procedure. I actually came up with this approach, I think yours may be better:
SELECT FormatIDKey FROM testTable
WHERE AttributeID = 12
INTERSECT
SELECT FormatIDKey FROM testTable
WHERE AttributeID = 23
EXCEPT
SELECT FormatIDKey FROM testTable
WHERE AttributeID = 26
Post #1480082
Posted Thursday, August 1, 2013 12:39 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 3:34 PM
Points: 3,513, Visits: 7,563
Actually, if you read the discussion of the article, you can see performance comparisions for several methods. Your code should be faster than mine (which was recommended by the article) but you could test for performance in your environment.



Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1480088
Posted Thursday, August 1, 2013 3:21 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 2:49 PM
Points: 2,030, Visits: 3,030
Luis Cazares (8/1/2013)
You could find this article useful
http://www.sqlservercentral.com/articles/T-SQL/88244/

SELECT AttribID
FROM MyTable
WHERE FormatID IN (12,15)
GROUP BY AttribID
HAVING COUNT(DISTINCT FormatID ) = 2
EXCEPT
SELECT AttribID
FROM MyTable
WHERE FormatID <> 27




The article's wrong, particularly this statement:
"In SQL Server 2000 and before, you needed to use a WHERE NOT EXISTS, WHERE NOT IN, or an OUTER JOIN with a NULL filter to do the exclusion."

HAVING can handle the whole check in ANY version of SQL that supports CASE (which even SQL 6.5 supported).


SELECT AttribID
FROM dbo.MyTable
WHERE
FormatID IN (12,15,27) --list ALL values that need tested, included and excluded
GROUP BY
AttribID
HAVING
MAX(CASE WHEN FormatID = 12 THEN 1 ELSE 0 END) = 1 AND --must be found
MAX(CASE WHEN FormatID = 15 THEN 1 ELSE 0 END) = 1 AND --must be found
MAX(CASE WHEN FormatID = 27 THEN 1 ELSE 0 END) = 0 --must NOT be found




SQL DBA,SQL Server MVP('07, '08, '09)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1480168
Posted Thursday, August 1, 2013 3:32 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:13 PM
Points: 13,282, Visits: 12,116
ScottPletcher (8/1/2013)
Luis Cazares (8/1/2013)
You could find this article useful
http://www.sqlservercentral.com/articles/T-SQL/88244/

SELECT AttribID
FROM MyTable
WHERE FormatID IN (12,15)
GROUP BY AttribID
HAVING COUNT(DISTINCT FormatID ) = 2
EXCEPT
SELECT AttribID
FROM MyTable
WHERE FormatID <> 27




The article's wrong, particularly this statement:
"In SQL Server 2000 and before, you needed to use a WHERE NOT EXISTS, WHERE NOT IN, or an OUTER JOIN with a NULL filter to do the exclusion."

HAVING can handle the whole check in ANY version of SQL that supports CASE (which even SQL 6.5 supported).


SELECT AttribID
FROM dbo.MyTable
WHERE
FormatID IN (12,15,27) --list ALL values that need tested, included and excluded
GROUP BY
AttribID
HAVING
MAX(CASE WHEN FormatID = 12 THEN 1 ELSE 0 END) = 1 AND --must be found
MAX(CASE WHEN FormatID = 15 THEN 1 ELSE 0 END) = 1 AND --must be found
MAX(CASE WHEN FormatID = 27 THEN 1 ELSE 0 END) = 0 --must NOT be found




This is discussed at length in the comments of that article. There are several other approaches to the same thing. Then Jeff does his million row tests against all the various methods. Might be worth digging through the comments to see the results and the various ways of accomplishing this.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1480174
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse