If you plop the following into Query Analyzer (assuming SqlServer200) I think you will
get what you want:
declare @cid int
set @cid = 2
if object_id('tempdb..#tblAttributeType') is not null drop table #tblAttributeType
if object_id('tempdb..#tblColor') is not null drop table #tblColor
if object_id('tempdb..#tblWheels') is not null drop table #tblWheels
if object_id('tempdb..#tblInterior') is not null drop table #tblInterior
if object_id('tempdb..#tblCarMake') is not null drop table #tblCarMake
if object_id('tempdb..#tblCarModel') is not null drop table #tblCarModel
if object_id('tempdb..#tblCar') is not null drop table #tblCar
if object_id('tempdb..#tblCar_Attributes') is not null drop table #tblCar_Attributes
create table #tblAttributeType (AttributeTypeId int, Attribute varchar(50))
insert into #tblAttributeType (attributeTypeId,Attribute) values(1,'Color')
insert into #tblAttributeType (attributeTypeId,Attribute) values(2,'wheels')
insert into #tblAttributeType (attributeTypeId,Attribute) values(3,'interior')
create table #tblColor (ColorId int, Color varchar(50))
insert into #tblColor (ColorId,Color) values(1,'Red')
insert into #tblColor (ColorId,Color) values(2,'Green')
insert into #tblColor (ColorId,Color) values(3,'Blue')
create table #tblWheels (WheelId int, Wheel varchar(50))
insert into #tblWheels (WheelId,Wheel) values(1,'Steel')
insert into #tblWheels (WheelId,Wheel) values(2,'Mag')
insert into #tblWheels (WheelId,Wheel) values(3,'Chrome')
create table #tblInterior (InteriorId int, Interior varchar(50))
insert into #tblInterior (InteriorId,Interior) values(1,'Cloth')
insert into #tblInterior (InteriorId,Interior) values(2,'Leather')
insert into #tblInterior (InteriorId,Interior) values(3,'Mohair')
create table #tblCarMake (CarMakeId int, Make varchar(50))
insert into #tblCarMake (CarMakeId,Make) values(1,'Honda')
insert into #tblCarMake (CarMakeId,Make) values(2,'Toyota')
insert into #tblCarMake (CarMakeId,Make) values(3,'Ford')
create table #tblCarModel(CarModelId int, Model varchar(50))
insert into #tblCarModel (CarModelId,Model) values(1,'civic')
insert into #tblCarModel (CarModelId,Model) values(2,'prelude')
insert into #tblCarModel (CarModelId,Model) values(3,'4Runner')
create table #tblCar (CarId int, CarMakeId int, CarModelId int, Vin int )
insert into #tblCar (CarId,CarMakeId,CarModelId) values(1,1,1)
insert into #tblCar (CarId,CarMakeId,CarModelId) values(2,2,3)
insert into #tblCar (CarId,CarMakeId,CarModelId) values(3,1,2)
create table #tblCar_Attributes (CarId int, AttributeTypeId int, Attribute int)
insert into #tblCar_Attributes (CarId, AttributeTypeId, Attribute) values
(2,1,2)
insert into #tblCar_Attributes (CarId, AttributeTypeId, Attribute) values
(2,2,1)
insert into #tblCar_Attributes (CarId, AttributeTypeId, Attribute) values
(2,3,2)
select tcm.Model,tcmk.Make,tc.Color,tw.Wheel,ti.Interior
from
#tblCar as tcar
inner join #tblCarModel as tcm
on tcm.CarModelId=tcar.CarModelId
inner join #tblCarMake as tcmk
on tcmk.CarMakeId=tcar.CarMakeId
inner join #tblColor as tc
on tc.ColorId = (select attribute from #tblCar_Attributes
where AttributeTypeId=1 and [EMAIL PROTECTED])
inner join #tblWheels as tw
on tw.WheelId = (select attribute from #tblCar_Attributes
where AttributeTypeId=2 and [EMAIL PROTECTED])
inner join #tblInterior as ti
on ti.InteriorId = (select attribute from #tblCar_Attributes
where AttributeTypeId=3 and [EMAIL PROTECTED])
where
[EMAIL PROTECTED]
��� Tony
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Host with the leader in ColdFusion hosting.
Voted #1 ColdFusion host by CF Developers.
Offering shared and dedicated hosting options.
www.cfxhosting.com/default.cfm?redirect=10481
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
