Query help - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

Query help

Author

Topic

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2010-07-28 : 05:12:21

Hi,

I have this working fine


SELECT A.CapId, ISNULL((SELECT F.CH FROM dbPubMatrix..tblNewMatrixFigures F 
WHERE F.VehicleRef = A.VehicleRef and F.maintained=0 and F.MilesPA=10000 and F.Term =3),0) as Price_3
FROM dbPubMatrix..tblNewMatrixArval A 
WHERE A.CapId IN (47106,47104,47105,47099,47097,47098,47101,47103,47100,47102)

I need to add other columns with different MilesPA, so I tried this


SELECT A.CapId, ISNULL((SELECT F.CH FROM dbPubMatrix..tblNewMatrixFigures F 
WHERE F.VehicleRef = A.VehicleRef and F.maintained=0 and F.MilesPA=10000 and F.Term =3),0) as Price_3
FROM dbPubMatrix..tblNewMatrixArval A 
WHERE A.CapId IN (47106,47104,47105,47099,47097,47098,47101,47103,47100,47102),
ISNULL((SELECT F.CH FROM dbPubMatrix..tblNewMatrixFigures F 
WHERE F.VehicleRef = A.VehicleRef and F.maintained=0 and F.MilesPA=20000 and F.Term =3),0) as Price_3
FROM dbPubMatrix..tblNewMatrixArval A 
WHERE A.CapId IN (47106,47104,47105,47099,47097,47098,47101,47103,47100,47102)

But that has an error, any help appreciated. Thanks.

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-07-28 : 06:00:00

you have doubled the query. try this:


SELECT A.CapId, ISNULL((SELECT F.CH FROM dbPubMatrix..tblNewMatrixFigures F 
WHERE F.VehicleRef = A.VehicleRef and F.maintained=0 and F.MilesPA=10000 and F.Term =3),0) as Price_3,
ISNULL((SELECT F.CH FROM dbPubMatrix..tblNewMatrixFigures F 
WHERE F.VehicleRef = A.VehicleRef and F.maintained=0 and F.MilesPA=20000 and F.Term =3),0) as Price_3_added_column
FROM dbPubMatrix..tblNewMatrixArval A 
WHERE A.CapId IN (47106,47104,47105,47099,47097,47098,47101,47103,47100,47102)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources