List:General Discussion« Previous MessageNext Message »
From:tony Date:April 6 1999 4:47pm
Subject:possible in MySQL without subselect?
View as plain text  
Further to my previous query about how to optimise the join for
this query, I now have a query about actually making the query
work better.

There are 2 relevant tables, sale and sale_items.

Sale contains the details of one sale to one customer, and
sale_items contains the details of each item in that order.

I want to know which items have been bought most often by 
people who have bought any other given item.

The current query is:

  SELECT STRAIGHT_JOIN si2.itemid, COUNT(si2.itemid) as ordered
    FROM sale_items as si1, sale as s1, sale as s2, sale_items as si2
   WHERE s1.saleid = si1.saleid
     AND s2.saleid = si2.saleid
     AND s1.customerid = s2.customerid
     AND si1.itemid = 7000000014273
     AND si2.itemid != 7000000014273
   GROUP BY si2.itemid
   ORDER BY ordered DESC, s2.when DESC

However, this gets skewered considerably when one person buys
multiple copies of an item. For instance if person A buys 100 copies
of item X, and 1 copy of item Y, then it will tell me that X is
the most popular item for people who have bought Y - even if A is the
only other to have bought X, and 80 individual people have bought
1 copy of Z. So I'm guessing I need to tailor the query to be based 
around the customerid more (which is in the sale table), but any
way I can think to do that involves a subselect or a COUNT DISTINCT,
neither of which MySQL supports

Now, I seem to remember someone claiming that any subselect could be
replicated in MySQL using LEFT JOINs, but I can't work out how to
do this one ...

Anyone?

Thanks,

Tony
-- 
-----------------------------------------------------------------------------
 Tony Bowden | Belfast, NI | tb@stripped | www.tmtm.com | www.blackstar.co.uk
                        If I'm feigning coherence and calmness  Laugh with me
-----------------------------------------------------------------------------
Thread
possible in MySQL without subselect?tony6 Apr
  • possible in MySQL without subselect?Michael Widenius10 Apr