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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 ANSI OUTER JOIN RECORD COUNT VARIATION
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

raguyazhin
Posting Yak Master

India
105 Posts

Posted - 11/03/2012 :  02:21:12  Show Profile  Reply with Quote
Hi,

I am Planning to convert the T-SQL Joins to ANSI Joins But the one of the below query having variation of record counts.kindly suggest me what are the problems in the below queries.

--T-SQL Join Query--


SELECT * FROM
dbo.CL_CLINICAL_MASTER SPM,
dbo.CL_ITEM_STOCK_MASTER BID,
dbo.CL_ITEM_MASTER IM,
dbo.CL_ITEM_CATEGORY UM
WHERE
SPM.CCM_CLINICAL_CD*= BID.CIS_CLINICAL_CD
AND IM.CIM_ITEM_CODE*= BID.CIS_ITEM_CD
AND IM.CIM_ITEM_CATEGORY_CD= UM.CIC_CATEGORY_CD

--5991 records return



--ANSI Join Query--

SELECT * FROM
CL_CLINICAL_MASTER AS SPM LEFT OUTER JOIN
CL_ITEM_STOCK_MASTER AS BID ON SPM.CCM_CLINICAL_CD = BID.CIS_CLINICAL_CD RIGHT OUTER JOIN
CL_ITEM_MASTER AS IM ON BID.CIS_ITEM_CD = IM.CIM_ITEM_CODE INNER JOIN
CL_ITEM_CATEGORY AS UM ON IM.CIM_ITEM_CATEGORY_CD = UM.CIC_CATEGORY_CD

--4684 records return


How do i match the ANSI Join Query to T-SQL Join Query.

--
Ragu

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 11/03/2012 :  10:55:13  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
how about

SELECT * FROM
CL_CLINICAL_MASTER AS SPM
cross join CL_ITEM_MASTER AS IM
join CL_ITEM_CATEGORY AS UM
on IM.CIM_ITEM_CATEGORY_CD = UM.CIC_CATEGORY_CD
LEFT JOIN CL_ITEM_STOCK_MASTER AS BID
ON SPM.CCM_CLINICAL_CD = BID.CIS_CLINICAL_CD
and IM.CIM_ITEM_CODE = BID.CIS_ITEM_CD

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

Ifor
Aged Yak Warrior

700 Posts

Posted - 11/05/2012 :  06:59:43  Show Profile  Reply with Quote
or maybe:


SELECT *
FROM dbo.CL_CLINICAL_MASTER SPM
	LEFT JOIN dbo.CL_ITEM_STOCK_MASTER BID
		ON SPM.CCM_CLINICAL_CD = BID.CIS_CLINICAL_CD
	RIGHT JOIN
	(
		dbo.CL_ITEM_MASTER IM
		JOIN dbo.CL_ITEM_CATEGORY UM
			ON IM.CIM_ITEM_CATEGORY_CD = UM.CIC_CATEGORY_CD
	)
		ON BID.CIS_ITEM_CD = IM.CIM_ITEM_CODE


The thing to remember with the *=/=* syntax is that the * is against the table where all the rows are preserved.
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.1 seconds. Powered By: Snitz Forums 2000