Show Data from 2 different Database

[ehpratah]

Hi need some insight on how to show record from different database on the same server. i also tried using join but didnt get the correct output. im finding a way to make it work using just one query. my last resort will be using different query to select the data i need(hope i wouldn't go that way)

what i have so far is this. it show the record of both database but somehow it repeating the same record

SELECT DB2.processed_trans.Ra_num , DB1.processed_trans.Ra_num FROM DB2.processed_trans JOIN DB1.processed_trans ;

thanks,

neil

[hericles]

You aren't using an ON criteria so you're getting a CROSS JOIN of the two tables, which is a cartesian product. That's why you are seeing rows duplicated. Adding an ON clause will fix it up.

[ehpratah]

Hi

Im just trying to show all the Ra_num record from both database, there is no matching record that i can use for the ON. am i missing something? is there other way around it?

[pritaeas]

Are you looking for UNION ?

[ehpratah]

to tell you the truth i dont know what to use. can i achieve what i want tru UNION? i'll try to look at union then.

[ehpratah]

Hi Thanks hericles and Pritaes both of you for the suggestion. UNION solved it all.

i came up with this

select Ra_num from DB1.processed_trans union all select Ra_num from DB2.processed_trans

now came the more pressing issue im trying to figure out.but because of your suggestion i think im on the right track already.

[pritaeas]

SELECT DB2.processed_trans.Ra_num FROM DB2.processed_trans 
UNION
SELECT DB1.processed_trans.Ra_num FROM DB1.processed_trans

If you want all records from tables from both DBs and there is no common id, you'll have to use something like UNION or UNION_ALL. Which depends on the data you need. UNION_ALL will give you "duplicate" records - where identical records are included (all columns the same) and UNION will strip these. UNION provides a bigger hit on performance as it has to match "duplicates".

An example would be:

(SELECT DB2.processed_trans.Ra_num, 'DB2' AS source FROM DB2.processed_trans)
UNION ALL
(SELECT DB1.processed_trans.Ra_num, 'DB1' AS source FROM DB1.processed_trans)

THat will include a 'source' column letting you know which db the data came from. Just an example. The ALL version should be quicker even though it's not required as records from each table should never be the same with the added column.

(SELECT DB2.processed_trans.Ra_num FROM DB2.processed_trans)
UNION
(SELECT DB1.processed_trans.Ra_num FROM DB1.processed_trans)

That will give you a no duplicate output.

[ehpratah]

Thanks guys!