06-22-2011, 05:38 AM
So I'm trying to query two tables (one is Recipes, the other Articles) and return the combined set of the 20 "newest" dates from the combined set. Here's what I have for explanation purposes, but I know it's not right... Any thoughts?
$sql = "SELECT RecipeName, Cuisine, URLName, RecipeType, DateEntered FROM Recipes UNION SELECT ArticleTitle, ArticleURL, DateEntered FROM Articles ORDER BY DateEntered DESC LIMIT 20";
06-22-2011, 06:18 AM
(1) All the SELECTs in a UNION must SELECT the same number of fields. If one or more fields doesn't/don't make sense for a given table, supply a NULL for that field.
(2) The TYPES of the fields must match in all SELECTs. That is, a number must match with a number, a date with a date, etc. There is some latitude here, especially with MySQL. For example, you can match an INT and a DOUBLE field. The result will always be DOUBLE, the type that the other field(s) can be coerced to.
(3) Only the names of the fields in the *FIRST* SELECT will be visible in the final result. The field names in the other SELECTs are ignored.
(4) If you want to have some way of determining, in the final result, what table a given record came from, you should supply that as an extra field in the SELECTs yourself.
SELECT 'R' AS theTable, RecipeName, Cuisine, URLName, RecipeType, DateEntered FROM Recipes
SELECT 'A', ArticleTitle, NULL, ArticleURL, NULL, DateEntered FROM Articles
ORDER BY DateEntered DESC
Notice that I used NULL for "RecipeName" and NULL for "RecipeType" in the SELECT from Articles.
Notice that the 'R' or 'A' will tell you which table a given record is from (though you could also do that by looking to see if RecipeName is NULL, for example).
06-22-2011, 06:32 AM
Works beautifully - and thanks so much for the in-depth explanation - took a second to get my head around it but once I did it really clicked... Thanks for taking your time to teach!!