masteroleary Posted April 9, 2007 Share Posted April 9, 2007 My goal is to select rows from two tables 'shoot' & 'video' and have mysql order those tables by date and limit the number of rows returned. I want to list the latest 10 rows but am uncertain how many rows from each table will be the latest. I may have more recent shoots than videos or the opposite. How can I use a single ORDER BY and LIMIT statement for both selections? This is the code I used last which may or may not be anywhere near the direction I need to go. SELECT id, name, description, directory, date FROM shoot LIMIT 3 UNION ( SELECT id, name, description, filename, date FROM video ) ORDER BY date LIMIT 0 , 30 Quote Link to comment Share on other sites More sharing options...
masteroleary Posted April 9, 2007 Author Share Posted April 9, 2007 Havent gotten a reply. Last time that happened I tried to do something mysql couldnt. Is this the case now? Quote Link to comment Share on other sites More sharing options...
per1os Posted April 9, 2007 Share Posted April 9, 2007 SELECT id, name, description, directory, `date` FROM shoot LIMIT 3 UNION ( SELECT id, name, description, filename, `date` FROM video ) ORDER BY `date` LIMIT 0,30 The limit you should not have any spaces. If you are using MySQL make sure you are using a version that supports subquerys. The date column can be considered a "special" word, using the backticks (`) to enclose it may also help. If that does not work do OR DIE(mysql_error()) and report back the error or show some code. Quote Link to comment Share on other sites More sharing options...
masteroleary Posted April 10, 2007 Author Share Posted April 10, 2007 Thanks Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.