outer join question - 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)

outer join question

Author

Topic

inibhreaslain
Starting Member

19 Posts

Posted - 2008-12-03 : 12:45:48

I have 2 tables
- QT_Main (QuoteID int, Segment nvarchar(20))
- qt_QuoteID_MaxStatus (QuoteID int, Status nvarchar(10))

I need to get ALL Segments with the number of records they have for a given Status ('Quoted' ni this case). How can I return ALL segments even if there are no QuoteIDs with Status='Quoted' in qt_QuoteID_MaxStatus

I have this:
SELECT m2.Segment, count(s2.QuoteID) AS Quoted
from QT_Main m2 left outer join qt_QuoteID_MaxStatus s2
on m2.QuoteID = s2.QuoteID
where s2.Status = 'Quoted'
GROUP BY m2.Segment

which gives me the data I need, but does not show Segments that have no 'Quoted' quotes. I alos tried on m2.QuoteID =* s2.QuoteID

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-03 : 12:58:01

[code]SELECT m2.Segment, count(s2.QuoteID) AS Quoted
from QT_Main m2
left outer join qt_QuoteID_MaxStatus s2
on m2.QuoteID = s2.QuoteID
AND s2.Status = 'Quoted'
GROUP BY m2.Segment
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources