Combing results with different format

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Combing results with different format

Author

Topic

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2008-01-02 : 06:38:49

I've got this table - TableA

ID CapID Make Model
1 36335 Ford Mondeo
2 12445 Audi A3
3 34443 BMW 3 Series
4 34334 Citroen C5

I need a result set which includes all tableA but with the addition of the following fields - all boolean. Alloys, Cruise, AirCon, Metallic

To get the Alloy fields I can use this statement

SELECT * FROM NVDStandardEquipment LEFT OUTER JOIN NVDDictionaryOption ON NVDStandardEquipment.SE_OptionCode = NVDDictionaryOption.DO_OptionCode WHERE NVDStandardEquipment.SE_Id = 36335 AND NVDStandardEquipment.SE_OptionCode=444

If true 1 row will be returned, if false nothing is returned. Obivously SE_OptionCode=444 is for Alloys, the others have different codes.

How can I combine these two into one usable view. Is it possible?

Thanks

georgev
Posting Yak Master

122 Posts

Posted - 2008-01-02 : 08:13:20

depends on your table structure...

Assuming the following
cars(carId, make, model)
options(carID, alloys, cruise, aircon, metallic)

Then you can use
[CODE]
SELECT c.carID
, c.make
, c.model
, o.alloys
, o.cruise
, o.aircon
, o.metallic
FROM cars c
LEFT
JOIN options o
ON o.carID = c.carID
[/CODE]

George
<3Engaged!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources