April 9, 2015 at 4:35 am
Hi - I'm working on a join between two tables where I only want one row returned...let me explain!
I'm matching on two columns between two tables. One of those columns in the target table could be null. I only want one record returned.
create table #vehicle(id int, vehiclemake varchar(10), vehiclemodel varchar(10), classtype varchar(1))
create table #class(id int, classtype varchar(1), value int)
insert into #vehicle values(1, 'AUDI', 'R8', 'A')
insert into #vehicle values(2, 'AUDI', null, 'B')
insert into #class values(1, 'A', 100)
insert into #class values(2, 'B', 1)
select
*
from
#vehicle v
inner join #class c on v.classtype = c.classtype
where
v.vehiclemake = 'AUDI'
and v.vehiclemodel = 'R8'
drop table #vehicle
drop table #class
Using the above example, if VehicleModel is anything other than 'R8' is specified then I want it to return the other class type record.
This is going to be used as a join within a bigger statement, so I'm not sure ordering and returning top 1 is going to work.
Any help greatly appreciated.
Cheeers
kan
Using the above example,
April 9, 2015 at 5:53 am
Quick suggestion (if I got this right)
😎
select
*
from
#vehicle v
inner join #class c on v.classtype = c.classtype
where
v.vehiclemake = 'AUDI'
and ( v.vehiclemodel = 'R8' OR v.vehiclemodel IS NULL);
Results
id vehiclemake vehiclemodel classtype id classtype value
----------- ----------- ------------ --------- ----------- --------- -----------
1 AUDI R8 A 1 A 100
2 AUDI NULL B 2 B 1
April 9, 2015 at 5:57 am
Hi - I only want 1 record returned.
So if the model is 'R8' it will return the A class type record, and if the model is anything else it will return the B class type record.
I tried putting
and isnull(v.vehiclemodel, @model) = @model into the 'where' clause but then if the @model is 'R8' then it matches to both class type records
Thanks
April 9, 2015 at 6:30 am
Ok - here's a better example:
create table #vehicle(id int, vehiclemake varchar(10), vehiclemodel varchar(10))
create table #vehicleclass(id int, vehiclemake varchar(10), vehiclemodel varchar(10), classtype varchar(1))
create table #class(id int, classtype varchar(1), value int)
insert into #vehicle values(1, 'AUDI', 'R8')
insert into #vehicle values(2, 'AUDI', null)
insert into #vehicle values(3, 'PORSCHE', null)
insert into #vehicleclass values(1, 'AUDI', 'R8', 'A')
insert into #vehicleclass values(2, 'AUDI', null, 'B')
insert into #class values(1, 'A', 100)
insert into #class values(2, 'B', 1)
insert into #class values(3, 'C', 0)
select
*
from
#vehicle v
left join #vehicleclass vc on v.vehiclemake = vc.vehiclemake and v.vehiclemodel = isnull(vc.vehiclemodel, v.vehiclemodel)
left join #vehicleclass c on vc.classtype = c.classtype
drop table #vehicle
drop table #vehicleclass
drop table #class
There should only be one record for the R8!
April 9, 2015 at 6:37 am
Update:
ok, I changed my first join so that it was:
left join #vehicleclass vc on v.vehiclemake = vc.vehiclemake and isnull(v.vehiclemodel, '') = isnull(vc.vehiclemodel, '')
But then if I add another record to the vehicle table:
insert into #vehicle values(2, 'AUDI', 'Quattro')
running the select statement doesn't give me a class record for the Quattro (should match to the model being null)
Grrrrrrr!
April 9, 2015 at 7:09 am
And here's the problem with NULL values. An absolute unknown can't equal an absolute unknown. Also, running functions on both columns like that will absolutely destroy performance. I'd strongly suggest getting default values of some kind on there in order to support the type of JOIN you need.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 9, 2015 at 7:18 am
Yeah, I know the problems with nulls. What I'm trying to do is have a 'catch all' record.
So in my example, I'm interested in Audi R8 and Audi Quattro, but all other Audi's should use the default 'null' model record.
I can't think of a simpler way to describe the data - creating a record for every single possible make/model with an associated class will be a nightmare to maintain - I just want the specifics and then a catch all...
April 9, 2015 at 8:17 am
We have a winner!
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')
insert into @vehicle values(2, 'AUDI', 'Quattro')
insert into @vehicle values(3, 'PORCHE', '')
insert into @vehicleclass values(1, 'AUDI', 'R8', 'A')
insert into @vehicleclass values(2, 'AUDI', null, 'B')
SELECT *
FROM @vehicle v
left JOIN @vehicleclass vc ON v.vehiclemake = vc.vehiclemake
AND ( v.vehiclemodel = vc.vehiclemodel
OR ( vc.vehiclemodel IS NULL
AND v.vehiclemodel NOT IN (
SELECT vehiclemodel
FROM @vehicleclass
WHERE vehiclemodel IS NOT NULL )
)
)
April 9, 2015 at 9:44 am
CROSS APPLY often works well in these types of lookups:
SELECT *
FROM @vehicle v
CROSS APPLY (
SELECT TOP (1) *
FROM @vehicleclass vc
WHERE
v.vehiclemake = vc.vehiclemake AND
(v.vehiclemodel = vc.vehiclemodel OR vc.vehiclemodel IS NULL)
ORDER BY
vehiclemodel DESC
) AS ca1
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 10, 2015 at 1:38 am
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
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply