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 3415 3419 3425 3412 4915 4927 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

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 3415 3419 3425 3412 4915 4927 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

ThanksBrian

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.

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 3415 3419 3425 3412 4915 4927 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

ThanksBrian

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?

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, 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 testTableWHERE AttributeID = 12INTERSECTSELECT FormatIDKey FROM testTableWHERE AttributeID = 23EXCEPTSELECT FormatIDKey FROM testTableWHERE AttributeID = 26

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?

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 AttribIDFROM dbo.MyTableWHERE FormatID IN (12,15,27) --list ALL values that need tested, included and excludedGROUP BY AttribIDHAVING 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."

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 AttribIDFROM dbo.MyTableWHERE FormatID IN (12,15,27) --list ALL values that need tested, included and excludedGROUP BY AttribIDHAVING 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.