Join Syntax - 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 2000 Forums

SQL Server Development (2000)

Join Syntax

Author

Topic

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2001-12-12 : 19:40:40

I need help converting my join syntax from one model to the other... I have a successfully working query that reads as:

select t1.Cntrct_ID, t3.Name1, t1.FileName, t1.File_Extension, t1.z_Cntrct_Doc_Type
FROM PS_ZPO_Cntrct_Docs t1
JOIN PS_Cntrct_Hdr t2 on t1.Cntrct_ID = t2.cntrct_ID
JOIN PS_Vendor t3 on t2.Vendor_SetID = t3.SetID and t2.Vendor_ID = t3.Vendor_ID
Where t1.Cntrct_ID = '0000000537'

This returns 4 rows. Now, I need to convert this to the syntax where the join references are in the WHERE clause. I attempted this with:

select t1.Cntrct_ID, t3.Name1, t1.FileName, t1.File_Extension, t1.z_Cntrct_Doc_Type
FROM PS_ZPO_Cntrct_Docs t1, PS_Cntrct_Hdr t2, PS_Vendor t3
Where t1.Cntrct_ID = '0000000537'
	AND t1.Cntrct_ID = t2.Cntrct_ID
	AND t2.Vendor_SetID = t3.SetID
	AND t2.Vendor_ID = T2.Vendor_ID

but it is returning 359508 rows (must have some sort of cross join happening here). Do any of you see what's wrong in here?

(And for those who will ask me WHY I want to make this change, I must regretfully inform you that this query will end up in an Oracle database, and it is my understanding that Oracle 8 only understands the second syntax structure. I know, I know, we don't do Oracle here, and I'd rather not deal with it at all, but thought somebody could throw me a bone on this

...)

Thanks! I'm working on DDL and DML in case we need it...

-------------------
It's a SQL thing...

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2001-12-12 : 19:45:20

You know, there's something magical about posting here... All of a sudden, you see your own typos... In the last AND statement, it's pointing t2 to t2 instead of t2 to t3. I fixed it and all is well.

Thanks for letting me "talk out loud". It must be quittin' time

!

-------------------
It's a SQL thing...

Edited by - AjarnMark on 12/12/2001 19:47:48