Grouping by columns to create single record

  • 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

  • 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[/url]

  • 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?

  • 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
  • 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.

  • 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

  • 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
  • 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) A socialist is someone who will give you the shirt off *someone else's* back.

  • 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 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