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...

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,...

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...

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...

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...

Perfect - works a treat. Amended it to fit my data set, even expanded it for a few more groups and voila.

Nice one, very much appreciated!

Oki doki - let me give that a try and see what happens!

Thanks for suggesting something....

I may not have explained myself properly

If I just want the sum per hour and join to my numbers table I get what I want:

hour ...