Exclude similar columns in a JOIN command

Question

Exclude similar columns in a JOIN command

RZ52

Mr or Mrs. 500

Points: 536
More actions
March 1, 2013 at 11:49 am

#290462

Hi,
I have three tables and the structures are :
Table1 : [PATIENT_ID],[Name],[Age],[City],[TEST1],[TEST2]...... (the AB? are test names dynamically added to table)
Table2 : [PATIENT_ID],[Name],[Age],[DR1],[DR2],...... (the DR? are drug names dynamically added to table)
Table3 : [Hospital],[City],[DATE],[ROOM],[PATIENT_ID]
I need to 'SELECT JOIN' them to create this table :
[Hospital],[City],[DATE],[ROOM],[PATIENT_ID],[Name],[Age],[TEST1],[TEST2]......, [DR1],[DR2],......
When I simply join them, I will have repeated columns such as [Name], [City]. Here, I simplified the structure but in reality we have many more such informative columns. In the other hand, part of tables are dynamically changing. So, it would be really difficult to define column names individually.
I appreciate for any guide to tell me if there is a way to exclude those similar columns and they show up only once.
Thanks in advance

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286573 More actions · Answer 1

RZ52 (3/1/2013)
Hi,
I have three tables and the structures are :
Table1 : [PATIENT_ID],[Name],[Age],[City],[TEST1],[TEST2]...... (the AB? are test names dynamically added to table)
Table2 : [PATIENT_ID],[Name],[Age],[DR1],[DR2],...... (the DR? are drug names dynamically added to table)
Table3 : [Hospital],[City],[DATE],[ROOM],[PATIENT_ID]
I need to 'SELECT JOIN' them to create this table :
[Hospital],[City],[DATE],[ROOM],[PATIENT_ID],[Name],[Age],[TEST1],[TEST2]......, [DR1],[DR2],......
When I simply join them, I will have repeated columns such as [Name], [City]. Here, I simplified the structure but in reality we have many more such informative columns. In the other hand, part of tables are dynamically changing. So, it would be really difficult to define column names individually.
I appreciate for any guide to tell me if there is a way to exclude those similar columns and they show up only once.
Thanks in advance

You have two things going on here. First is your data is not normalized. Meaning you shouldn't have the patient name and such in more than 1 location. Also, I see that you have chosen to store AGE. This is a terrible idea. Do you go through the tables and update them daily to make sure everybody's age is current? For this type of thing you should store birthdate and calculate age.

The second issue is that I am guessing you are using select *? You should always specify only the columns you want from your queries.

If you need much more specific information you are going to need to post way more details.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

RZ52 Mr or Mrs. 500 Points: 536 More actions · Answer 2

Sean Lange (3/1/2013)
RZ52 (3/1/2013)
Hi,
I have three tables and the structures are :
Table1 : [PATIENT_ID],[Name],[Age],[City],[TEST1],[TEST2]...... (the AB? are test names dynamically added to table)
Table2 : [PATIENT_ID],[Name],[Age],[DR1],[DR2],...... (the DR? are drug names dynamically added to table)
Table3 : [Hospital],[City],[DATE],[ROOM],[PATIENT_ID]
I need to 'SELECT JOIN' them to create this table :
[Hospital],[City],[DATE],[ROOM],[PATIENT_ID],[Name],[Age],[TEST1],[TEST2]......, [DR1],[DR2],......
When I simply join them, I will have repeated columns such as [Name], [City]. Here, I simplified the structure but in reality we have many more such informative columns. In the other hand, part of tables are dynamically changing. So, it would be really difficult to define column names individually.
I appreciate for any guide to tell me if there is a way to exclude those similar columns and they show up only once.
Thanks in advance
You have two things going on here. First is your data is not normalized. Meaning you shouldn't have the patient name and such in more than 1 location. Also, I see that you have chosen to store AGE. This is a terrible idea. Do you go through the tables and update them daily to make sure everybody's age is current? For this type of thing you should store birthdate and calculate age.
The second issue is that I am guessing you are using select *? You should always specify only the columns you want from your queries.
If you need much more specific information you are going to need to post way more details.

Dear Sean,

Thanks for suggestions.

As you might guess, these data relates to an hospital information system. The three tables are from three different sections working on three independent databases and I need to unite them. This is why there are repeated columns.

The [Age] refers the age of patient at the time of reception for tests or prescribing medication and it is constant.

I can't specify column's name because part of two tables (Table1 and Table2) are dynamically changing. It means as a new test is added to system, a new column will be added to table1 for its result.

Anyway, I am not the designer of this system and I am just asked to make an app that could build a connection between these three.

Any advice will be appreciated.

Thanks

Sean Lange SSC Guru Points: 286573 More actions · Answer 3

What a nightmare!!! Nothing like creating queries against a moving target. I know it isn't your design but the nightmare you are dealing with is because of the lack of normalization. The details should be in another table and then there is no need to add/remove columns all the time.

At any rate, about the only way you are going to have a chance here is with dynamic sql. Something like this should be a decent starting point.

--First we have to generate the list of distinct column names

declare @Columns varchar(max)

select @Columns = Stuff((

select ',' + name

from sys.columns

where object_id = object_id('table1')

or object_id = object_id('table2')

group by name, column_id

order by column_id

for XML path(''), type).value('.', 'varchar(max)'), 1, 1, '')

select @Columns

--Now we need to select these columns.

declare @sql varchar(max)

set @sql = 'select ' + @Columns + ' from '

+ 'table1 '

+ 'join table2 on table1.column = table2.column'

select @sql

--Once you are satisfied the sql string is built correctly you need to execute it

--exec sp_executesql @sql

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

RZ52 Mr or Mrs. 500 Points: 536 More actions · Answer 4

Dear Sean,

Thanks for help.

I know how much headache I will get to find a stable solution but your suggestion is a really helpful starting point.

I will work on it to develop desired (hopefully) the final answer.

Thanks again.

Sean Lange SSC Guru Points: 286573 More actions · Answer 5

RZ52 (3/1/2013)
Dear Sean,
Thanks for help.
I know how much headache I will get to find a stable solution but your suggestion is a really helpful starting point.
I will work on it to develop desired (hopefully) the final answer.
Thanks again.

You are welcome. Please make sure that you pay very close attention to what you are doing since you are doing this via dynamic sql. If you have any filters or anything (like content for a where clause) that can come from an outside source you MUST add parameters to your dynamic sql. Dynamic sql is incredibly powerful but that power comes at the cost of added risk of sql injection. As long as you understand what you are doing there is no reason you can't generate a working solution (I would say optimal but the designer of the systems prevented that ;-)).

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/