SQL query multiple columns

I have a table with patient records. There is one column with primary diagnose and 20 columns with secondary diagnoses.

I often receive order to get  a list of patients with this and  that diagnose regardless primary or secondary. For now I use query for each diagnose column combined by "OR".

My question is if there is more elegant way to query?

Looking forward to hearing from you.

Kostas Vilimas
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rajkumar GsSoftware EngineerCommented:
Two ways

SELECT * FROM YourTable 
WHERE Column1 = 'Diagnosis1' 
OR Column2 = 'Diagnosis1' 
OR ...

Open in new window


SELECT * FROM YourTable WHERE Column1 = 'Diagnosis1' 
SELECT * FROM YourTable WHERE Column2 = 'Diagnosis1' 

Open in new window

Better you write query in these two different way. By analysising the execution plan, you can identify which would be best.
Query Menu - Enable 'Actual Execution Plan' and run the query.
Rajkumar GsSoftware EngineerCommented:
Second query should be using 'UNION ALL' - But make sure diagnosis data is not duplicated in different columns of the same row. If so this query would return duplicate data.
SELECT * FROM YourTable WHERE Column1 = 'Diagnosis1' 
SELECT * FROM YourTable WHERE Column2 = 'Diagnosis1' 

Open in new window

Don't really see how you would go about that. I believe that the only way for you to filter the information you want is with or...

There is however one other way I can think of. You can concatenate all the colums you want into one single variable, and then simply check the contents of that variable using '=' or 'like'. this way you only have one item on your Where clause, but I don't believe that it will make the process faster.
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

Rajkumar GsSoftware EngineerCommented:
>> You can concatenate all the colums you want into one single variable, and then simply check the contents of that variable using '=' or 'like'

I think that could cause improper output.
For eg:- If we search by the word 'Diagnosis' and if more that one Diagnosis data has this word as part of their name, it will that data as well. Correct approach would be using '='

I believe the query that asker is using now would be better.
I am not sure about my second query's performance.
Your're right what I proposed is not the best way to handle it.. as it could even make the query slower.. I was just trying to provide an alternative.. there is nothing wrong with multiple Or's / And's.
with your current design you have to specify the OR's between each diagnosis column...

a better design would probably be to have the diagnosis on a separate table (or at least the secondary ones...)

then you would query the additional table searching a single column... using an exists subquery...


can you tell us more about the background to the requirement/system...

e.g. without a business (medical?) understanding of the reasons why you have a primary and (up to?) 20 secondary diagnosis
it will be difficult to properly assess the true nature of the system...

what is the datatype of the diagnosis column? is it character (and you are doing character/text searches) or is it a coded reference to some "medical" condition?

probably of importance for us/(the system) is the need to understand  the lifecycle of a diagnosis... do they change of overtime... do secondaries become primaries and vice-versa?

Anthony PerkinsCommented:
>>My question is if there is more elegant way to query?<<
As Lowfatspread has indicated a better way would be to normalize your database by adding an additional table.
If you cannot change the schema of the tables then you can certainly do it with a UNION query, but it will end up being a dog.

Scott PletcherSenior DBACommented:
You can simplify the actual coding like so:

    'diagnosis_to_match_1' IN (pimary_diagnosis, secondary_diagnosis_1, secondary_diag_2, ...) AND
    'diagnosis_to_match_2' IN (pimary_diagnosis, secondary_diagnosis_1, secondary_diag_2, ...)

The final executable code will be the same, but it will be much easier for you to code the query, as you only have to change each diagnosis to match once.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kvilimasAuthor Commented:
this was the closest to what I have asked
the elegant method is to design the tables properly in the first instance
also without more information on the actual background/intention of your system we are "stumped"
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.