>>>>> "tony" == tony <tony@stripped> writes:
tony> Further to my previous query about how to optimise the join for
tony> this query, I now have a query about actually making the query
tony> work better.
tony> There are 2 relevant tables, sale and sale_items.
tony> Sale contains the details of one sale to one customer, and
tony> sale_items contains the details of each item in that order.
tony> I want to know which items have been bought most often by
tony> people who have bought any other given item.
tony> The current query is:
tony> SELECT STRAIGHT_JOIN si2.itemid, COUNT(si2.itemid) as ordered
tony> FROM sale_items as si1, sale as s1, sale as s2, sale_items as si2
tony> WHERE s1.saleid = si1.saleid
tony> AND s2.saleid = si2.saleid
tony> AND s1.customerid = s2.customerid
tony> AND si1.itemid = 7000000014273
tony> AND si2.itemid != 7000000014273
tony> GROUP BY si2.itemid
tony> ORDER BY ordered DESC, s2.when DESC
tony> However, this gets skewered considerably when one person buys
tony> multiple copies of an item. For instance if person A buys 100 copies
tony> of item X, and 1 copy of item Y, then it will tell me that X is
tony> the most popular item for people who have bought Y - even if A is the
tony> only other to have bought X, and 80 individual people have bought
tony> 1 copy of Z. So I'm guessing I need to tailor the query to be based
tony> around the customerid more (which is in the sale table), but any
tony> way I can think to do that involves a subselect or a COUNT DISTINCT,
tony> neither of which MySQL supports
tony> Now, I seem to remember someone claiming that any subselect could be
tony> replicated in MySQL using LEFT JOINs, but I can't work out how to
tony> do this one ...
You can't use LEFT JOIN to simulate any sub selects; The truth is
that many sub selects are only versions of normal JOIN or left joins.
You can on the other hand solve most sub selects by the use of
Try first to figure out how you should write what you want to do WITH
sub selects and after this how you can convert it to a normal select
(with or without a temporary table)