From: Peter Brawley
Date: February 25 2006 2:54am
Subject: Re: Inner join with left join
List-Archive: http://lists.mysql.com/mysql/195168
Message-Id: <43FFC6D5.9030502@earthlink.net>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="=======AVGMAIL-43FFC6D50CCA======="
--=======AVGMAIL-43FFC6D50CCA=======
Content-Type: multipart/alternative; boundary=------------080600090400040508090501
--------------080600090400040508090501
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Scott,
I think Shawn nailed it with
SELECT
p.id, p.prod_name,
SUM(IF(o.id IS NULL,0,oi.quantity)) AS Qty
FROM products AS p
INNER JOIN order_items AS oi ON (p.id = oi.product_id)
INNER JOIN orders as o ON (oi.order_id = o.id)
AND o.status NOT IN ('cancelled', 'pending', 'ghost')
AND o.created BETWEEN "2006-02-23 00:00:00" AND "2006-02-23 23:59:59"
GROUP BY p.id, p.prod_name
(you can often speed up a join by moving conditions from the Where
clause to the unnamed side of an Inner or Left Join).
PB
-----
Scott Haneda wrote:
>> Is this what you mean?
>>
>> SELECT
>> p.prod_name,
>> count(oi.product_id) AS mycount
>> FROM ORDERS AS o
>> INNER JOIN products ON o.id=p.id
>> LEFT JOIN order_items AS oi ON (p.id = oi.product_id)
>> WHERE o.status NOT IN ('cancelled', 'pending', 'ghost')
>> GROUP BY oi.product_id
>> ORDER BY mycount;
>>
>
> Well, sort of, here is what I managed to coble together, which gets me
> pretty close, it is just what I want, other than it is missing products with
> a zero count. This tells me those products have not been ordered ever, but
> I would like to know what they are.
>
> SELECT o.id, oi.prod_name, sum(oi.quantity) as qty
> FROM orders as o
> INNER JOIN order_items as oi
> ON (o.id = oi.order_id)
> LEFT JOIN products as p
> ON (p.id = oi.product_id)
> WHERE o.status NOT IN ('cancelled', 'pending', 'ghost')
> AND
> (o.created BETWEEN "2005-01-01 00:00:00" AND "2006-02-22 23:59:59")
> GROUP BY oi.product_id
> ORDER by qty ASC
>
>
>
--------------080600090400040508090501
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Scott,
I think Shawn nailed it with
SELECT
p.id, p.prod_name,
SUM(IF(o.id IS NULL,0,oi.quantity)) AS Qty
FROM products AS p
INNER JOIN order_items AS oi ON (p.id = oi.product_id)
INNER JOIN orders as o ON (oi.order_id = o.id)
AND o.status NOT IN ('cancelled', 'pending', 'ghost')
AND o.created BETWEEN "2006-02-23 00:00:00" AND "2006-02-23 23:59:59"
GROUP BY p.id, p.prod_name
(you can often speed up a join by moving conditions from the Where
clause to the unnamed side of an Inner or Left Join).
PB
-----
Scott Haneda wrote:
Is this what you mean?
SELECT
p.prod_name,
count(oi.product_id) AS mycount
FROM ORDERS AS o
INNER JOIN products ON o.id=p.id
LEFT JOIN order_items AS oi ON (p.id = oi.product_id)
WHERE o.status NOT IN ('cancelled', 'pending', 'ghost')
GROUP BY oi.product_id
ORDER BY mycount;
Well, sort of, here is what I managed to coble together, which gets me
pretty close, it is just what I want, other than it is missing products with
a zero count. This tells me those products have not been ordered ever, but
I would like to know what they are.
SELECT o.id, oi.prod_name, sum(oi.quantity) as qty
FROM orders as o
INNER JOIN order_items as oi
ON (o.id = oi.order_id)
LEFT JOIN products as p
ON (p.id = oi.product_id)
WHERE o.status NOT IN ('cancelled', 'pending', 'ghost')
AND
(o.created BETWEEN "2005-01-01 00:00:00" AND "2006-02-22 23:59:59")
GROUP BY oi.product_id
ORDER by qty ASC
--------------080600090400040508090501--
--=======AVGMAIL-43FFC6D50CCA=======
Content-Type: text/plain; x-avg=cert; charset=us-ascii
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
Content-Description: "AVG certification"
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.1.0/269 - Release Date: 2/24/2006
--=======AVGMAIL-43FFC6D50CCA=======--