Cedric
>Try:
>
>select StatusName, count(t.status)
>from ticketstatus s
>left outer join tickets as t
> on s.statusid = t.status
> and t.owner = 2
>group by statusName
>
>Steve
>
>
>-----Original Message-----
>From: Cedric Villat [mailto:[EMAIL PROTECTED]
>Sent: Friday, January 30, 2004 3:58 PM
>To: CF-Talk
>Subject: Getting rid of subquery
>
>
>Can anyone help me with getting rid of this subquery?
>
><cfquery>
>SELECT Count(t.Status) AS Count, StatusName
>FROM TicketStatus s LEFT OUTER JOIN
> (SELECT Status
> FROM Tickets t
> WHERE t.Owner = 2) t
> ON t.Status = s.StatusID
>GROUP BY StatusName
></cfquery>
>
>Right now, all of the StatusNames are shown, even if their count is 0. I've
>tried just about every join I can think of and it always returns the
>StatusNames that have a count greater than 0. I want ALL the StatusNames,
>even ones with a count of 0. Anyone?
>
>Cedric
> _____
>
>
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
