Slightly different approach, only scans each table once

😎

USE tempdb;

GO

SET NOCOUNT ON;

declare @vehicle table(id int, vehiclemake varchar(10), vehiclemodel varchar(10));

declare @vehicleclass table(id int, vehiclemake varchar(10), vehiclemodel varchar(10), classtype varchar(1));

insert into @vehicle values

(1, 'AUDI', 'R8')

,(2, 'AUDI', 'Quattro')

,(3, 'PORCHE', '')

;

insert into @vehicleclass values

(1, 'AUDI', 'R8', 'A')

,(2, 'AUDI', null, 'B')

;

;WITH MATCHED_AND_ORDERED AS

(

SELECT

V.id AS V_id

,V.vehiclemake AS V_vehiclemake

,V.vehiclemodel AS V_vehiclemodel

,VC.id AS VC_id

,VC.vehiclemake AS VC_vehiclemake

,VC.vehiclemodel AS VC_vehiclemodel

,VC.classtype AS VC_classtype

,ROW_NUMBER() OVER

(

PARTITION BY V.id

ORDER BY CASE

WHEN V.vehiclemodel = VC.vehiclemodel THEN 1

WHEN VC.vehiclemodel IS NULL AND V.vehiclemodel IS NOT NULL THEN 2

ELSE 3

END

) AS VC_RID

FROM @vehicle V

LEFT OUTER JOIN @vehicleclass VC

ON V.vehiclemake = VC.vehiclemake

)

SELECT

MAO.V_id

,MAO.V_vehiclemake

,MAO.V_vehiclemodel

,MAO.VC_id

,MAO.VC_vehiclemake

,MAO.VC_vehiclemodel

,MAO.VC_classtype

FROM MATCHED_AND_ORDERED MAO

WHERE MAO.VC_RID = 1;

Results

V_id V_vehiclemake V_vehiclemodel VC_id VC_vehiclemake VC_vehiclemodel VC_classtype

----------- ------------- -------------- ----------- -------------- --------------- ------------

1 AUDI R8 1 AUDI R8 A

2 AUDI Quattro 2 AUDI NULL B

3 PORCHE NULL NULL NULL NULL