LEFT OUTER JOIN - 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 2005 Forums

Transact-SQL (2005)

LEFT OUTER JOIN - help..

Author

Topic

steinster
Starting Member

5 Posts

Posted - 2009-04-22 : 23:32:34

Hi,

I cant seem to get this query to return what I want;

SELECT ima.IMA_recno,
COUNT(obj.sysObj_recno) AS 'Count'
FROM IMA_ImageAlbum ima
LEFT OUTER JOIN sysObject_Relation obj ON ima.IMA_recno = CAST(obj.sysObj_val1 AS INT)
WHERE CONTAINS(obj.sysObj_relationGroupName, 'ImageInAlbum')
GROUP BY ima.IMA_recno

It works great without the CONTAINS syntax, but the count is a bit off, so I guess it doesnt work great but.. ;)

With the CONTAINS it wont return any rows from IMA thats not found in sysObject_Relation.
In the case of no reference I want it to return 0.

Anyone ??

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-23 : 01:11:17

SELECT ima.IMA_recno,
COUNT(obj.sysObj_recno) AS 'Count'
FROM IMA_ImageAlbum ima
LEFT OUTER JOIN sysObject_Relation obj ON ima.IMA_recno = CAST(obj.sysObj_val1 AS INT)
and CONTAINS(obj.sysObj_relationGroupName, 'ImageInAlbum')
GROUP BY ima.IMA_recno

E 12°55'05.63"
N 56°04'39.26"

steinster
Starting Member

5 Posts

Posted - 2009-04-23 : 01:23:14

thanks! :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources