Comparison - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Comparison

Author

Topic

Magua1
Starting Member

15 Posts

Posted - 2014-07-07 : 05:42:57

I have a table where I need to do some comparisons.
Table has 8 fields fld1...fld8.
e.g I want to return all records where fld1=1 and any other fld2...8=5 or fld2=1 and fld1 or fld3...8=5 etc.
i.e. if any field=1 and any other field=5 then return that record.
I know I could write a long query comparing everything but is there a better way to do this sort of thing?
TIA
Alan

sz1
Aged Yak Warrior

555 Posts

Posted - 2014-07-07 : 07:35:20

Select *
From YourTable
Where fld1 = 1
Or fld2 = 1
Or fld3 In (1,5)
Or fld4 = 1
Or fld5 = 1
Or fld6 = 1
Or fld7 = 1
Or fld8 In (1,5)

#########################

Select *
From YourTable
Where fld1 In (1,5)
Or fld2 In (1,5)
Or fld3 In (1,5)
Or fld4 In (1,5)
Or fld5 In (1,5)
Or fld6 In (1,5)
Or fld7 In (1,5)
Or fld8 In (1,5)

We are the creators of our own reality!

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2014-07-07 : 08:26:04

You can use this also
Select *
From YourTable
Where (
1 IN (fld1, fld2, fld3,...fld8)
OR
5 IN (fld1, fld2, fld3,...fld8)
)

"There is only one difference between a dream and an aim.
A dream requires soundless sleep to see,
whereas an aim requires sleepless efforts to achieve..!!"

Magua1
Starting Member

15 Posts

Posted - 2014-07-07 : 10:06:14

That worked great.
Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources