SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Grouping by columns to create single record


Grouping by columns to create single record

Author
Message
BFSTEP
BFSTEP
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 14
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 Angry

Thanks
Brian
Eugene Elutin
Eugene Elutin
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11912 Visits: 5478
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 Angry

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!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
batgirl
batgirl
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2121 Visits: 1820
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 Angry

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?
Luis Cazares
Luis Cazares
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40515 Visits: 19811
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.
General Disclaimer:
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?


How to post data/code on a forum to get the best help: Option 1 / Option 2
BFSTEP
BFSTEP
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 14
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.
BFSTEP
BFSTEP
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 14
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
Luis Cazares
Luis Cazares
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40515 Visits: 19811
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.
General Disclaimer:
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?


How to post data/code on a forum to get the best help: Option 1 / Option 2
ScottPletcher
ScottPletcher
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18931 Visits: 7407
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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial:
If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.
Sean Lange
Sean Lange
SSC Guru
SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)

Group: General Forum Members
Points: 60202 Visits: 17952
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 Modens 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)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search