MySQL Forums
Forum List  »  Newbie

Re: JOIN Usage
Posted by: Felix Geerinckx
Date: May 03, 2005 11:57AM

Sean S wrote:

> I want to write a SELECT statement that returns all of the dispositions and counts those that
> match. So I'd like to end up with something like...
> Strong 15
> Refused Treat./Transp. 10
> Highland 2
> Lakeside 0
> Park Ridge 3
> RGH 5
> St. Mary's 0
> Nursing home 0
> Other med. Facility 0
> Residence 0
> Transfer Care to Other 6
> Call Cancelled 6
> Standby 10
> No patient found 1
> Other 1
> Police/Jail 0
> Morgue 0
> Provided Care with Other 0
>
> I have come up with this,
> SELECT
> Dispositions.Disposition,
> Dispositions.dispositionID,
> COUNT(Calls.dispositionID) AS DispositionCount
> FROM Dispositions
> LEFT JOIN Calls ON Dispositions.dispositionID =
> Calls.dispositionID
> WHERE Calls.date > '2005-03-31'
> AND Calls.date < '2005-05-01'
> AND ( Calls.ambulanceID IS NOT NULL
> OR Calls.dispositionID = '2'
> OR Calls.dispositionID = '12' )
> GROUP BY Calls.dispositionID
> ORDER BY Dispositions.Disposition

If you want all records from your Dispositions table in the resultset, you should move the conditions on the Calls table from the WHERE clause to the ON clause in tle LEFT JOIN.

--
felix

Options: ReplyQuote


Subject
Written By
Posted
May 03, 2005 08:50AM
Re: JOIN Usage
May 03, 2005 11:57AM
May 03, 2005 12:06PM
May 03, 2005 12:16PM
May 03, 2005 12:20PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.