kvilimas
asked on
SQL query multiple columns
Hi,
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.
Regards,
Kostas Vilimas
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.
Regards,
Kostas Vilimas
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'
UNION ALL
SELECT * FROM YourTable WHERE Column2 = 'Diagnosis1'
UNION ALL
....
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.
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.
>> You can concatenate all the colums you want into one single variable, and then simply check the contents of that variable using '=' or 'like'
@APoPhySpt:
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.
@APoPhySpt:
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.
@RajkumarGS,
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.
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...
however...
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?
hth
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...
however...
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?
hth
>>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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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"
also without more information on the actual background/intention of your system we are "stumped"
Open in new window
OROpen 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.