|
|
Posted by rojelio on 12/03/07 15:15
On Nov 29, 7:16 am, Toby A Inkster <usenet200...@tobyinkster.co.uk>
wrote:
> Rik Wasmus wrote:
> > (LEFT) JOINS are usually faster (and more portable).
>
> If I understand the structure correctly, then the table consists of
> basically these columns:
>
> user_id
> product_id
> access_date
>
> With perhaps some other columns (IP address, User-Agent, etc) but we're
> ignoring them for now. Multiple accesses to a product by a single user are
> recorded as multiple rows in the table. If that is indeed the case, then I
> don't see how the requested information (for each product, a list of users
> sorted in order of last access time, and indicating a count of accesses)
> can be produced without using a subquery.
>
> Joins will of course come in handy too, as you probably don't just want to
> include the user's ID and product ID, but want to include their names and
> perhaps some other info too:
>
> SELECT
> p.product_id,
> p.product_name,
> u.user_id,
> u.fullname,
> u.email_address,
> sub.last_access,
> sub.count_access
> FROM (
> SELECT
> user_id,
> product_id,
> MAX(access_date) AS last_access,
> COUNT(*) AS count_access
> FROM product_accesses
> GROUP BY user_id, product_id
> ) AS sub
> LEFT JOIN products p
> ON sub.product_id=p.product_id
> LEFT JOIN users u
> ON sub.user_id=u.user_id
> ORDER BY sub.product_id, sub.last_access DESC
>
> Yes, joins are fast, but if the information can't be extracted in a single
> query with joins, then using a subquery or two is still likely to be
> significantly faster than just pulling all the data into PHP and doing the
> processing there, or (heaven forbid!) pulling off part of the data, and
> then performing a second query for each row of the original dataset!
>
> And regarding portability, any database that doesn't support subqueries
> belongs on a scrapheap. (Or if it's got its original packaging in good
> condition, maybe a museum of antiques and curiosities.)
>
> --
> Toby A Inkster BSc (Hons) ARCS
> [Geek of HTML/SQL/Perl/PHP/Python/Apache/Linux]
> [OS: Linux 2.6.17.14-mm-desktop-9mdvsmp, up 4 days, 19:45.]
> [Now Playing: Ryan Adams - Love Is Hell]
>
> Sharing Music with Apple iTunes
> http://tobyinkster.co.uk/blog/2007/11/28/itunes-sharing/
Thanks
Toby you're a cornucopia of help.
[Back to original message]
|