CODEWITHSUNDEEP
sqlruby-on-railspostgresql
mrben522
mrben522

Reputation: 417

How to merge data from two tables with some common fields

So I have two tables that have similar data. There are columns in table A that match columns in table B, but with different naming conventions, there are also columns from each that have no equivalent in the other table, no rows should be merged, I need a view (I think) with all the rows from both tables, but with some columns merged so that data from table A.columnB and data from table B.columnF both end up in view C.columnD. There would be columns in the view that only had sources in one of the tables and would be null in rows from the other table. I can't change any of the existing table structure as the database is shared across multiple apps. I think I need to use a bunch of FULL OUTER JOIN statements in the view but I'm having trouble wrapping my mind around how to really go about it. If anyone can provide a generic example of how this should look I should be able to take it from there.

Here's an example of what doesn't work (there are a lot more columns on each side of the JOIN in the actual db, truncated for readability):

SELECT
    schedule_block.id as vid,
    schedule_block.reason as vreason,
    schedule_block.when_ts as vwhen,
    schedule_block.duration as vduration,
    schedule_block.note as vnote,
    schedule_block.deleted_ts as vdeleted_when,
    schedule_block.deleted_user_id as vdeleted_user_id,
    schedule_block.lastmodified_ts as vlastmodified_ts,
    schedule_block.lastmodified_user_id as vlastmodified_user_id
FROM schedule_block
FULL OUTER JOIN appointment.appt_when as vwhen ON 1 = 1
FULL OUTER JOIN appointment.patient_id  as vpatient ON 1 = 1
FULL OUTER JOIN appointment.duration as vduration on 1 = 1
FULL OUTER JOIN appointment.deleted_when as vdeleted_when ON 1 = 1

Correct me if I'm wrong but I think I can't use a UNION because there are different numbers of columns on each side

Upvotes: 0

Views: 44

Answers (1)

HoneyBadger
HoneyBadger

Reputation: 15150

You could do something like:

SELECT ColA, CONVERT(DATE, NULL) AS ColB
FROM T1
UNION ALL
SELECT CONVERT(VARCHAR(10), NULL) AS ColA, ColB
FROM T2

Just make sure to match the datatypes.

Upvotes: 1

Related Questions