# Thread: Do not show records if..

1. Registered User
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. King of Understatement
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?

3. King of Understatement
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

4. Cavalier King Charles
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
WHERE qb.cCol < 5
Last edited by izyrider; 09-17-06 at 14:22. Reason: illiteracy

5. King of Understatement
Join Date
Feb 2004
Location
One Flump in One Place
Posts
14,912
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

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)

#### Posting Permissions

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