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
karlhscAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Ephraim WangoyaConnect With a Mentor Commented:

select *
from car
inner join Car_new on car_new.id = car.ID and Car_new.CarNewColor <> Car.Car.CarColor
0
 
CluskittConnect With a Mentor Commented:
SELECT Car.CarID, Car.CarColor, Car_new.CarNewColor
FROM Car INNER JOIN Car_New
ON Car.CarID=Car_new.CarNewID AND Car.CarColor<>Car_new.CarNewColor
0
 
Ephraim WangoyaCommented:

select *
from car
inner join Car_new on car_new.carnewid = car.carID and Car_new.CarNewColor <> Car.CarColor
0
 
Aaron ShiloChief Database ArchitectCommented:
select * from (
select CarID,
case when car.CarColor<>new_car.carcolor then new_car.carcolor else 'X' end carcolor
from car join car_new
on car.id=car_new.id)
where carcolor <> 'X'
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.