August 1, 2013 at 11:05 am
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
August 1, 2013 at 11:16 am
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)
August 1, 2013 at 11:21 am
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?
August 1, 2013 at 11:30 am
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
August 1, 2013 at 11:36 am
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.
August 1, 2013 at 12:26 pm
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
August 1, 2013 at 12:39 pm
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.
August 1, 2013 at 3:21 pm
Luis Cazares (8/1/2013)
You could find this article usefulhttp://www.sqlservercentral.com/articles/T-SQL/88244/
SELECT AttribIDFROM 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) "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".
August 1, 2013 at 3:32 pm
ScottPletcher (8/1/2013)
Luis Cazares (8/1/2013)
You could find this article usefulhttp://www.sqlservercentral.com/articles/T-SQL/88244/
SELECT AttribIDFROM 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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply