karlhsc
asked on
SQL Join Question
Hi, I'm new to T-SQL joins and was wondering how one would build a SQL query that compares two tables and identifies changes between the two.
Example: I have listed two examples tables below. The SQL query should compare the records between each table (by ID) looking for changes in color between the two entries. The SQL query should return the the values for the CarID, CarColor, and CarNewColor for the rows with an ID of "1" only because this entry's color changed from red to orange (is different between the two tables). In the example below, the entries with ID of 2 and 3 did not change color.
Thank you in advance.
Car
CarID CarColor
1 red
2 red
3 blue
Car_new
CarNewID CarNewColor
1 orange
2 red
3 blue
Example: I have listed two examples tables below. The SQL query should compare the records between each table (by ID) looking for changes in color between the two entries. The SQL query should return the the values for the CarID, CarColor, and CarNewColor for the rows with an ID of "1" only because this entry's color changed from red to orange (is different between the two tables). In the example below, the entries with ID of 2 and 3 did not change color.
Thank you in advance.
Car
CarID CarColor
1 red
2 red
3 blue
Car_new
CarNewID CarNewColor
1 orange
2 red
3 blue
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
select * from (
select CarID,
case when car.CarColor<>new_car.carc olor then new_car.carcolor else 'X' end carcolor
from car join car_new
on car.id=car_new.id)
where carcolor <> 'X'
select CarID,
case when car.CarColor<>new_car.carc
from car join car_new
on car.id=car_new.id)
where carcolor <> 'X'
select *
from car
inner join Car_new on car_new.carnewid = car.carID and Car_new.CarNewColor <> Car.CarColor