• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 271
  • Last Modified:

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
0
karlhsc
Asked:
karlhsc
  • 2
2 Solutions
 
Ephraim WangoyaCommented:

select *
from car
inner join Car_new on car_new.id = car.ID and Car_new.CarNewColor <> Car.Car.CarColor
0
 
CluskittCommented:
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now