query logic

Hi there

Could you help me with rewritting below query in ANSI format-- for sql server 2005

SELECT
VSL.VSLNM, VSL.UVN, VSL.UnqCoNo, Co.CoRootNm, Vsl.VslSt, VslTy.VslTyNm, VslSt, Vsl.FlagCd, Vsl.YrBld,
Vsl.YrBld, Vsl.Grtge, Vsl.DdWtTge, Vsl.FmaDdWt
FROM
VSL, Co, VslCo, VslTy
WHERE
VSL.VSLNM = @.VslNm and
Vsl.VslTyCd = VslTy.VslTyCd and
VSL.UVN *= VslCo.UVN and
Co.CoNo =* (Select VslCo1.MainHdgOwrCoNo
from VSL VSL1,VslCo VslCo1, VslTy VslTy1
where VSL1.UVN = VSL.UVN and
Vsl1.VslTyCd = VslTy1.VslTyCd and
VslCo1.UVN = VSL1.UVN)

Regards

Rahul

The general form would be:

SELECT t1.Col1, t2.Col1

FROM Table1 t1

INNER JOIN Table2 t2

ON t1.ID = t2.ID

WHERE t1.Col3 = @.MyParameter

AND t1.Col4 = (SELECT t3.Col1

FROM MyTable3 t3

INNER JOIN MyTable4 t4

ON t3.ID = t4.ID)

See SQL Server 2005 Books Online topic: ‘FROM (Transact-SQL)’ for more details.

|||*= means "LEFT JOIN" and =* means "RIGHT JOIN" (I think)

So what you're after is:

SELECT
VSL.VSLNM, VSL.UVN, VSL.UnqCoNo, Co.CoRootNm, Vsl.VslSt, VslTy.VslTyNm, VslSt, Vsl.FlagCd, Vsl.YrBld,
Vsl.YrBld, Vsl.Grtge, Vsl.DdWtTge, Vsl.FmaDdWt
FROM
VSL
JOIN

VslTy
on Vsl.VslTyCd = VslTy.VslTyCd
RIGHT JOIN

Co

on Co.CoNo = (Select VslCo1.MainHdgOwrCoNo
from
VSL VSL1
JOIN
VslCo VslCo1
on VslCo1.UVN = VSL1.UVN
JOIN VslTy VslTy1
on Vsl1.VslTyCd = VslTy1.VslTyCd
where VSL1.UVN = VSL.UVN
)

LEFT JOIN

VslCo
on VSL.UVN = VslCo.UVN
WHERE
VSL.VSLNM = @.VslNm

But because you have RIGHT JOIN and LEFT JOIN used together, you will want to confirm that it's doing the right thing. Best is to eliminate RIGHT JOINs, in favour of LEFT, because it's so much clearer for all involved.

And it's been so long since I've used =* and *=... I guess I could have them the wrong way around.|||

well *= means left outer join

But above trick didnt work, It is really making me scratching my head.

|||

SELECT

VSL.VSLNM

, VSL.UVN

, VSL.UnqCoNo

, Co.CoRootNm

, Vsl.VslSt

, VslTy.VslTyNm

, VslSt

, Vsl.FlagCd

, Vsl.YrBld

, Vsl.YrBld

, Vsl.Grtge

, Vsl.DdWtTge

, Vsl.FmaDdWt

FROM

VSL

JOIN

VslTy

ON

Vsl.VslTyCd = VslTy.VslTyCd

LEFT JOIN

VslCo

ON

VSL.UVN = VslCo.UVN

RIGHT JOIN

Co

ON

CoNo = (Select

VslCo1.MainHdgOwrCoNo

FROM

VSL VSL1

,VslCo VslCo1

, VslTy VslTy1

WHERE

VSL1.UVN = VSL.UVN

AND

Vsl1.VslTyCd = VslTy1.VslTyCd

AND

VslCo1.UVN = VSL1.UVN

)

WHERE

VSL.VSLNM = @.VslNm

|||Ok, try swapping where I've put 'LEFT' and 'RIGHT' - my definition may be the wrong way around. I guess =* could be LEFT... it's been too long.

Rob|||

Hi there

Many thanx to you all, with ur input I am able to crack it.

Right query is

SELECT

VSL.VSLNM, VSL.UVN, VSL.UnqCoNo, Co.CoRootNm, Vsl.VslSt, VslTy.VslTyNm, VslSt, Vsl.FlagCd, Vsl.YrBld,

Vsl.YrBld, Vsl.Grtge, Vsl.DdWtTge, Vsl.FmaDdWt

FROM

VSL Join VSLTy

on vsl.vsltycd=vslty.vsltycd

left join vslco

on vsl.uvn=vslco.uvn

left join co

on

co.cono=(Select VslCo1.MainHdgOwrCoNo

from VSL VSL1,VslCo VslCo1, VslTy VslTy1

where VSL1.UVN = VSL.UVN and

Vsl1.VslTyCd = VslTy1.VslTyCd and

VslCo1.UVN = VSL1.UVN)

where

VSL.VSLNM = @.VslNm