Combining two table into a single table

I have two tables with a 1 to many relationship:

tblCarCompanies
ID Company
1 Mazda
2 Nissan

tblCarModels
ID Company_fk Model
1 1 Miata
2 1 Mazda3
3 2 Sentra
4 2 Pathfinder
5 2 Maxima

What's the best way to query these two table into one result set like:
tblCars
Company Models
Mazda Miata, Mazda3
Nissan Sentra, Pathfinder, Maxima

Maybe something like this:

declare @.carCompany table
( ID integer,
Company varchar(10)
)
insert into @.carCompany
select 1, 'Mazda' union all
select 2, 'Nissan'

declare @.carModel table
( ID integer,
Company_fk integer,
Model varchar(12)
)
insert into @.carModel
select 1, 1, 'Miata' union all
select 2, 1, 'Mazda3' union all
select 3, 2, 'Sentra' union all
select 4, 2, 'Pathfinder' union all
select 5, 2, 'Maxima'

select company,
reverse(substring(reverse(
( select model + ', ' as [text()]
from @.carModel b
where a.id = b.company_Fk
order by model
for xml path('')
)), 3, 200)) as Models
from @.carCompany a

/*
company Models
- --
Mazda Mazda3, Miata
Nissan Maxima, Pathfinder, Sentra
*/

|||One thing I noticed is that if my Models field contains an "&" it will return "&".
I'm guessing it has to do with the for xml path('') conversion. I can simply do a replace(myOutput,'&','&') but I'm not sure if it'll affect any other characters.

What exactly does the for xml path do and is there a way to convert it back without the replace?
|||Yes, I have seen aberations before because of the path(''); you definitely need to look out for it and you might even need to choose a different solution if it becomes a significant problem. Another alternative is to use a function -- preferably an inline function -- in conjunction with the CROSS APPLY operator. Would you like to see an example of such an alternative?|||Sure, an example would be great. Thanks!
|||

I didn't come up with a good way to create an INLINE function for this. Maybe somebody else sees a straight-forward way to do this. I mocked up this test with these tables:

create table dbo.carCompany
( ID integer,
Company varchar(10)
)
go
insert into dbo.carCompany
select 1, 'Mazda' union all
select 2, 'Nissan'
go

create table dbo.carModel
( ID integer,
Company_fk integer,
Model varchar(12)
)
go
insert into dbo.carModel
select 1, 1, 'Miata' union all
select 2, 1, 'Mazda3' union all
select 3, 2, 'Sentra' union all
select 4, 2, 'Pathfinder' union all
select 5, 2, 'Maxima'
go

An example of a scalar function is like this:

alter function dbo.listModels
( @.prm_companyID integer
)
returns varchar(300)
as
begin

declare @.modelList varchar(300)

if not exists
( select 0 from dbo.carModel
where company_fk = @.prm_companyID
)
return @.modelList

set @.modelList = ''

select @.modelList = @.modelList
+ model + ', '
from dbo.carModel
where company_fk = @.prm_companyID

set @.modelList = reverse(substring(reverse(@.modelList), 3, 300))

return @.modelList

end

go

select id,
dbo.listModels (id) as Models
from carCompany

/*
id Models
--
1 Miata, Mazda3
2 Sentra, Pathfinder, Maxima
*/

An example with a table function and cross apply is like:

alter function dbo.companyModels
( @.prm_companyID integer
)
returns @.companyModels table
( modelList varchar(300)
)
as
begin

declare @.modelList varchar(300)

if not exists
( select 0 from dbo.carModel
where company_fk = @.prm_companyID
)
return

set @.modelList = ''

select @.modelList = @.modelList
+ model + ', '
from dbo.carModel
where company_fk = @.prm_companyID

insert into @.companyModels
select reverse(substring(reverse(@.modelList), 3, 300))

return

end

go

select id,
m.modelList as Models
from carCompany
cross apply dbo.companyModels (id) m

/*
id Models
-- --
1 Miata, Mazda3
2 Sentra, Pathfinder, Maxima
*/

There are a couple of additional things to note:

It is critical to these functions that you have an index on the MODEL table based on COMPANY_FK; otherwise, you will table scan You might be able to get away with a NOLOCK optimizer hint in these functions; if you are not sure, do NOT add the NOLOCK hint.