May 13, 2010 at 11:05 am
I hope I can explain this correctly. I have two tables that I need to join together to see if any rows are new or have changed. Table A is the source table where the data orginally came from. Table B is the final table. So I need to compare B to A and determine if there are any new records or anything that was updated.
Here is sample data:
Table A
ID----Type---Number
123----EL-----123
Table B
ID----Type---Number
123----EL-----987
Right now table B has a number of 987..but in the source table A it was changed to 123. Since table B is the final table I want to track history. So I need table B to look like this:
ID----Type---Number
123----EL-----123
123----EL-----987
But I keep getting 1 row in there and not having both.
Currently im using MS SQL server (table A) and hitting oracle (table B)- Field names are different.
Sample query:
Select id , type ,number
from dbo.telephone2
inner join
openquery (tst, 'select ID_NBR ,
PHONE_TYPE ,PHONE_NBR
from Member_Telephone')
on id = ID_NBR
Please help.
May 13, 2010 at 12:06 pm
a slight variation on your original code; all i've really done is present more data, so you can see the changed columns;
since you never showed the PHONE_NBR from the other table, you couldn't see if it was changed or not.
i also changed to a left outer join so you can see stuff that is not in the openquery.
Select
Alias1.id ,
Alias1.type ,
Alias1.number ,
Alias2.ID_NBR ,
Alias2.PHONE_TYPE ,
Alias2.PHONE_NBR
from dbo.telephone2 Alias1
LEFT OUTER JOIN
openquery (tst, 'select
ID_NBR ,
PHONE_TYPE ,
PHONE_NBR
from Member_Telephone')Alias2
on Alias1.id = Alias2.ID_NBR
AND Alias1.type = Alias2.PHONE_TYPE
Lowell
May 13, 2010 at 12:20 pm
But I only want to show the 3 columns and have 2 distinct rows. I tried your way before and it showed both number but they were on the same line. Any other suggestions?
May 13, 2010 at 12:24 pm
you had joined them, thought that's what you wanted;
try a UNION ALL instead:
Select
Alias1.id ,
Alias1.type ,
Alias1.number ,
Alias2.ID_NBR ,
Alias2.PHONE_TYPE ,
Alias2.PHONE_NBR
from dbo.telephone2 Alias1
UNION ALL
SELECT
openquery (tst, 'select
ID_NBR ,
PHONE_TYPE ,
PHONE_NBR
from Member_Telephone')Alias2
Lowell
May 17, 2010 at 5:31 am
Or this might help you... I have used Temporary tables to give the idea....
The logic is same as of Lowell.
Declare @vTableA Table (ID int, [Type] varchar(10), Number int )
Declare @vTableB Table (ID int, [Type] varchar(10), Number int )
Insert into @vTableA
Select 123,'EL', 123
Insert into @vTableB
Select 123,'EL', 987
Select Distinct * from (
Select * from @vTableA
Union All
Select * from @vTableB
) MAin
May 18, 2010 at 1:09 am
[font="Verdana"]
Right now table B has a number of 987..but in the source table A it was changed to 123. Since table B is the final table I want to track history. So I need table B to look like this:
ID----Type---Number
123----EL-----123
123----EL-----987
If you want to maintain history, then why you dont keep separate table and how come TableB is main table iin your case?
--Mahesh
[/font]
MH-09-AM-8694
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply