Table joining question

[SpiderFlight]

I don't write many queries so I'm not sure if this join will do what I want. I have 2 tables on different databases. Each table contains hospital specific data. I want to combine the 2 tables and report off this one table. My dba will not allow a new table to be created containing the data from both tables. The tables have similar columns but not identical so a UNION query won't work. What would the join look like if I was to create a view?

FROM HospA.dbo.charges FULL OUTER JOIN
HospB ON HospB.case_mix_id <> HospB.case_mix_id

Will this join, in essence make the tables appear as one?

Thank you in advance.

 

[Qik3Coder]

if you need the similar data out of both then just left join the table on whatever values you need and use case statements to collapse the data from two columns into one:

Select case when A.col1 is not null then A.col1 else B.col1 end
from DB1.dbo.table A left join DB2.dbo.table B on 1=1
where hospital_name in(A.hospital_name, B.hospital_name)

Without Tek-Tips I would go Codal
-implementing random bugs for the sake of something to do.

 

[SQLSister]

I'd still probably use a union query. I fthe fields are not exact then you need to specify the ones you need for the query and put int a null value for the table which does not have that column.
Something like:

select field1, field2, field3, null as field4 from table1
union all
select field7, field2, null, field4 from table2

In this case table one contains field1, field2, and field2
and tablle 2 contains field7 (which contains the same type of data as field1 but has a differnt fieldname), field2 and field4. Union al lis more efficient if you knwo the tables are mutually exclusive.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[zeevgetner]

the question is if the same record has information in both tables.

it it does then full outer join is the way to go,
but you should write:
FROM HospA.dbo.charges FULL OUTER JOIN
HospB ON HospB.case_mix_id = HospB.case_mix_id

if those are compleately diffrent records,
then you should follow SQLSister advice