Cant get this join syntax right

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)

Cant get this join syntax right

Author

Topic

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2008-01-16 : 10:53:18

I've got this beast of a statement

SELECT * FROM (SELECT P.vehicleref,P.manufacturer,P.model,P.derivative,P.additionalfreetext,P.isLimited,P.isPriceReduction,P.isSpecial, P.created, P.updated, P.capiD,NMF.term, NMF.milespa, NMF.maintained, NMF.ch, ROW_NUMBER() OVER ( PARTITION BY P.VehicleRef ORDER BY NMF.ch ) AS MinCH FROM ( SELECT * FROM vwAllMatrixWithLombardSimple ) P INNER JOIN dbPubMatrix..tblNewMatrixFigures NMF ON P.VehicleRef = NMF. [VehicleRef] WHERE type='car') Q WHERE MinCH = 1 ORDER BY ch

I need to join dbPubMatrix..tblCarImages.StudioImages IMGS ON P.capID = IMGs.capID

I've tried loads of ways, but always with syntax errors.

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-16 : 10:58:51

[code]SELECT q.vehicleref,
q.manufacturer,
q.model,
q.derivative,
q.additionalfreetext,
q.isLimited,
q.isPriceReduction,
q.isSpecial,
q.created,
q.updated,
q.capiD,
q.term,
q.milespa,
q.maintained,
q.ch
FROM (
SELECT P.vehicleref,
P.manufacturer,
P.model,
P.derivative,
P.additionalfreetext,
P.isLimited,
P.isPriceReduction,
P.isSpecial,
P.created,
P.updated,
P.capiD,
NMF.term,
NMF.milespa,
NMF.maintained,
NMF.ch,
ROW_NUMBER() OVER (PARTITION BY P.VehicleRef ORDER BY NMF.ch) AS MinCH
FROM vwAllMatrixWithLombardSimple AS P
INNER JOIN dbPubMatrix..tblNewMatrixFigures as NMF ON NMF.[VehicleRef] = P.VehicleRef
INNER JOIN dbPubMatrix..tblCarImages.StudioImages AS IMGS ON IMGs.capID = P.capID
WHERE {p | nmf}.type = 'car'
) as Q
WHERE q.MinCH = 1
ORDER BY q.ch[/code]

E 12°55'05.25"
N 56°04'39.16"

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2008-01-17 : 04:43:09

Thanks Peso,

I get this

Incorrect syntax near '|'.

Cheers

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-01-17 : 04:59:37

quote:
{p | nmf}

column type is from which table ? vwAllMatrixWithLombardSimple or dbPubMatrix..tblNewMatrixFigures ?
You suppose to change this to the actual table alias where column type is from.

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources