Help with query - 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

Help with query

Author

Topic

Magua1
Starting Member

15 Posts

Posted - 2014-06-26 : 12:10:43

Hi

I have a SQL Server 2005 table as follows
RecNum varchar(6) main varchar(1) f1 varchar(1)...f8 varchar(8)

main can contain 'X', '1'...'8' or null
f1 can only contain '1' or null
f2 can only contain '2' or null
etc
If main contains '3' then there should be a corresponding '3' in the f3 field,
If main contains '7' then there should be a corresponding '7' in the f7 field.
etc
I want to list all the records where there is not a corresponding 'f'
record but excluding recs where main is 'X' or null

I've tried a few things with CASE's but can't seem to come up with something that works.

Any help appreciated.
TIA
Alan

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-06-26 : 15:02:47

Try this:

select *
  from yourtable
 where not (main is null
    or      main='X'
    or      (main='1' and main=f1)
    or      (main='2' and main=f2)
    or      (main='3' and main=f3)
    or      (main='4' and main=f4)
    or      (main='5' and main=f5)
    or      (main='6' and main=f6)
    or      (main='7' and main=f7)
    or      (main='8' and main=f8)
           )

Magua1
Starting Member

15 Posts

Posted - 2014-06-26 : 15:31:30

Thanks for you help
Alan

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-06-26 : 16:05:46

This is screaming for a database design change.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-06-27 : 08:47:27

Excluding NULL or X in Main column?


SELECT  *
FROM    dbo.Table
WHERE   Main NOT IN ('X', F1, F2, F3, F4, F5, F6, F7, F8)

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources