Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2006
    Location
    Calgary, AB
    Posts
    8

    Unanswered: Do not show records if..

    [field] AND [field] AND field] = [field] AND [field] AND field]

    I have 7 fields and I want to exclude results where 5 of these 7 fields are all equal.

    Anyone?
    Thanks

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Interesting problem. I presume you mean if any 5 of the 7 are equal? Do you consider NULL to equal NULL? Are they booleans? If so then it would be easy enough mathematically. Otherwise what is the datatype and the range of values?

    A final thought - might it be easier to include those records where 3 of those 7 fields are different?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Gosh - Sunday morning inspiration - so this is what it is like when you wake up on a weekend without a hangover.

    I tested this in SQL Server. As such I've included my DDL. It will work in Access too - you will only be interested in the query in red. There is at least one other solution to this problem but it is very wordy. Perhaps someone can think of something better too. As mentioned below, if there are a very few values (such as if the field is boolean) then other solutions present themselves.

    Code:
    CREATE TABLE Seven
        (
        TheID Int,
        Col1 Int,
        Col2 Int,
        Col3 Int,
        Col4 Int,
        Col5 Int,
        Col6 Int,
        Col7 Int
        )
     
    INSERT INTO Seven
    SELECT  1,
        1, 1, 1, 1, 1, 1, 1
    UNION ALL
    SELECT  2,
        NULL, 1, 1, 1, 2, 2, 3
    UNION ALL
    SELECT  3,
        NULL, NULL, NULL, NULL, NULL, NULL, 2
     
    SELECT  *
    FROM Seven
    WHERE   EXISTS  (SELECT TheID
            FROM
                (SELECT Col1, TheID
                FROM Seven
                Union
                SELECT  Col2, TheID
                FROM Seven
                Union
                SELECT  Col3, TheID
                FROM Seven
                Union
                SELECT  Col4, TheID
                FROM Seven
                Union
                SELECT  Col5, TheID
                FROM Seven
                Union
                SELECT  Col6, TheID
                FROM Seven
                Union
                SELECT  Col7, TheID
                FROM    Seven) AS DerT
            WHERE DerT.TheID = Seven.TheID
            GROUP BY
                TheID
            HAVING COUNT(*) >=3)
              
    DROP TABLE Seven
    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    nice try Poots
    but don't you miss cases like 1,1,1,1,2,2,2
    ...only two different values but not 5 values equal.

    here is my thrash at it:

    SELECT qb.TheID
    FROM (
    SELECT qa.TheID, qa.Col, count(qa.Col) AS cCol
    FROM [
    SELECT TheID, Col1 AS Col FROM seven
    UNION ALL
    SELECT TheID, Col2 AS Col FROM seven
    UNION ALL
    SELECT TheID, Col3 AS Col FROM seven
    UNION ALL
    SELECT TheID, Col4 AS Col FROM seven
    UNION ALL
    SELECT TheID, Col5 AS Col FROM seven
    UNION ALL
    SELECT TheID, Col6 AS Col FROM seven
    UNION ALL
    SELECT TheID, Col7 AS Col FROM seven
    ]. AS qa
    GROUP BY qa.TheID, qa.Col
    ) AS qb
    WHERE qb.cCol > 4


    izy

    LATER: ummm 'exclude results where 5 of these 7 fields are all equal'
    reading skills deserted me for a while!
    last line
    WHERE qb.cCol > 4
    should read
    WHERE qb.cCol < 5
    Last edited by izyrider; 09-17-06 at 14:22. Reason: illiteracy
    currently using SS 2008R2

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by izyrider
    nice try Poots
    but don't you miss cases like 1,1,1,1,2,2,2
    ...only two different values but not 5 values equal.
    Good point

    But If I add your data so we insert
    Code:
     
    SELECT  1,
        1, 1, 1, 1, 1, 1, 1
    UNION ALL
    SELECT  2,
        NULL, 1, 1, 1, 2, 2, 3
    UNION ALL
    SELECT  3,
        NULL, NULL, NULL, NULL, NULL, NULL, 2
    UNION ALL
    SELECT  4,
        1, 1, 1, 1, 2, 2, 2
    I get
    TheID
    -------
    1

    returned - where all the values are the same.

    BTW - it occurred to me ealier - this sort of question usually implies an unnormalised design.....

    EDIT - you edited your post while I wrote mine! Yup - nice solution
    Last edited by pootle flump; 09-17-06 at 15:36. Reason: Error (attributable to slow typing) ;0)
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •