...

View Full Version : Selecting from two diff table with diff columns...



chornbeck
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";

Old Pedant
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.

So:


SELECT 'R' AS theTable, RecipeName, Cuisine, URLName, RecipeType, DateEntered FROM Recipes
UNION
SELECT 'A', ArticleTitle, NULL, ArticleURL, NULL, DateEntered FROM Articles
ORDER BY DateEntered DESC
LIMIT 20

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).

chornbeck
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!!



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum