In article <[EMAIL PROTECTED]>, Scott Gifford <[EMAIL PROTECTED]> writes:
> The problem we're seeing is that when additional tables are pulled in > for the detailed view, the order is different from the summary view, > so the wrong homes are displayed. Here's a simplified example. A > summary query might ask: mysql> SELECT lutar_homes.mls_num, lutar_images.num_images, > lutar_homes_supplemental.address, > lutar_homes.listdate > FROM lutar_homes, lutar_homes_supplemental > LEFT JOIN lutar_images ON lutar_homes.mls_num = > lutar_images.mls_num > WHERE ((lutar_homes.listdate >= (NOW() - INTERVAL '14 00:00:00' > DAY_SECOND))) > AND lutar_homes.mls_num = lutar_homes_supplemental.mls_num > ORDER BY lutar_homes.listdate DESC > LIMIT 1; > +---------+------------+-------------------------+------------+ > | mls_num | num_images | address | listdate | > +---------+------------+-------------------------+------------+ > | 051768 | 1 | 7540 Country Pride Lane | 2005-05-31 | > +---------+------------+-------------------------+------------+ > 1 row in set (0.00 sec) > When I add one more LEFT JOIN clause (the second one below) to get > additional fields for the detailed view, I get a different first home, > even though none of the search parameters have changed, and the table > hasn't changed: mysql> SELECT lutar_homes.mls_num, lutar_images.num_images, > lutar_homes_supplemental.address, > lutar_homes.listdate > FROM lutar_homes, lutar_homes_supplemental > LEFT JOIN lutar_images ON lutar_homes.mls_num = > lutar_images.mls_num > LEFT JOIN lutar_homes_stats ON lutar_homes.mls_num = > lutar_homes_stats.mls_num > WHERE ((lutar_homes.listdate >= (NOW() - INTERVAL '14 00:00:00' > DAY_SECOND))) > AND lutar_homes.mls_num = lutar_homes_supplemental.mls_num > ORDER BY lutar_homes.listdate DESC > LIMIT 1; > +---------+------------+------------------+------------+ > | mls_num | num_images | address | listdate | > +---------+------------+------------------+------------+ > | 051770 | 9 | 9149 Frankenmuth | 2005-05-31 | > +---------+------------+------------------+------------+ > 1 row in set (0.02 sec) > This change in ordering screws up my system, since if the user clicked > on the first result in the summary view, the detailed view may display > a completely different home. The ordering does not change. You told the syetem to order by listdate DESC, and that's exactly what the system does. Your problem is that the listdate ordering is not complete - there can be multiple homes with the same listdate. LIMIT is only stable if 1. the tables don't change and 2. the ordering is complete If the additional tables joined in don't generate multiple rows for one home, it's enough to add the home's primary key to your ORDER BY. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
