Re: JOIN Usage
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
Subject
Written By
Posted
Re: JOIN Usage
May 03, 2005 11:57AM
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.