List:General Discussion« Previous MessageNext Message »
From:SGreen Date:February 22 2006 9:45pm
Subject:Re: Inner join with left join
View as plain text  
Scott Haneda <lists@stripped> wrote on 02/22/2006 03:58:10 PM:

> > 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
> 
> 

You just need to invert a couple of things...

SELECT p.id, p.prod_name, sum(oi.quantity) as qty
FROM Products p
LEFT JOIN orders as o
        ON (p.id = oi.product_id)
        AND o.created BETWEEN "2005-01-01 00:00:00" AND "2006-02-22 
23:59:59"
        AND o.status not IN ('cancelled', 'pending', 'ghost')
LEFT JOIN order_items as oi
        ON (o.id = oi.order_id)
GROUP BY p.id
ORDER by qty ASC


That should give you a list of all products and a count of how many have 
been ordered between 2005-01-01 and 2006-02-22 23:59:59 where the status 
of the order is neither 'cancelled', 'pending', or 'ghost'.

The think to remember is that an ON clause can be as complex as a WHERE 
clause. The ON clause also determines which rows of which table 
participate in a JOIN. In this case the only table to be affected will be 
the one on the right side of a LEFT join (in an INNER join both tables are 
filtered). So you keep all of your products visible (as declared in the 
FROM clause) and optionally associate with each product an order and 
optionally past that to an order_item. 
HTH!

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Thread
Inner join with left joinScott Haneda22 Feb
  • Re: Inner join with left joinSGreen22 Feb
  • MySQL Queries within OscommerceAM COMS22 Feb
    • Re: MySQL Queries within OscommerceSGreen22 Feb
  • Re: Inner join with left joinPeter Brawley22 Feb
    • Re: Inner join with left joinScott Haneda22 Feb
      • Re: Inner join with left joinSGreen22 Feb
        • Re: Inner join with left joinScott Haneda23 Feb
          • Re: Inner join with left joinJames Harvard23 Feb
            • Re: Inner join with left joinScott Haneda23 Feb
            • Re: Inner join with left joinSGreen23 Feb
              • Re: Inner join with left joinScott Haneda23 Feb
                • Re: Inner join with left joinSGreen23 Feb
                  • Re: Inner join with left joinSGreen23 Feb
                • Re: Inner join with left joinPeter Brawley23 Feb
      • Re: Inner join with left joinPeter Brawley25 Feb