On 05-Apr-2003 Chris Fowler wrote:
> Hello All,
>
> I'm trying to determine some marketing statistics using 3 tables
> (sales_opp_source, sales_opp, contract). I want to show ALL of the
> sales_opp_source records and show how many accepted contracts there
> have been for each of those sales_opp_source's (including the ones that
> would be 0).
>
> sales_opp_source
> -------------------------
> id
> name
>
> sales_opp
> --------------
> id
> sales_opp_source_id
>
> contract
> -----------
> id
> sales_opp_id
> status
>
> I basically need to do a LEFT JOIN from sales_opp_source to the results
> of an INNER JOIN between sales_opp and contract, but in the following
> code, I only get back the sales_opp_source's that actually have a
> contract. I want to get back all of the sales_opp_source's whether they
> have a sales_opp/contract or not.
>
No, I think you want 2 left joins:
sales_opp_source } sales_opp } contract
SELECT name, COUNT(contract.id) AS contract_count
FROM sales_opp_source
LEFT JOIN sales_opp ON sales_opp_source.id = sales_opp.sales_opp_source_id
LEFT JOIN contract ON sales_opp.id = contract.sales_opp_id
GROUP BY name;
+------------+----------------+
| name | contract_count |
+------------+----------------+
| billboard | 0 |
| classified | 2 |
| newspaper | 1 |
| radio | 0 |
+------------+----------------+
4 rows in set (0.02 sec)
(BTW, I think your field names are horrid ...)
Regards,
--
Don Read [EMAIL PROTECTED]
-- It's always darkest before the dawn. So if you are going to
steal the neighbor's newspaper, that's the time to do it.
(53kr33t w0rdz: sql table query)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
