Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    Regular Coder
    Join Date
    Feb 2006
    Posts
    138
    Thanks
    11
    Thanked 0 Times in 0 Posts

    Selecting from two diff table with diff columns...

    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?

    Code:
    $sql = "SELECT RecipeName, Cuisine, URLName, RecipeType, DateEntered FROM Recipes UNION SELECT ArticleTitle, ArticleURL, DateEntered FROM Articles ORDER BY DateEntered DESC LIMIT 20";

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,210
    Thanks
    75
    Thanked 4,343 Times in 4,309 Posts
    (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:
    Code:
    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).

  • Users who have thanked Old Pedant for this post:

    chornbeck (06-22-2011)

  • #3
    Regular Coder
    Join Date
    Feb 2006
    Posts
    138
    Thanks
    11
    Thanked 0 Times in 0 Posts
    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!!


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •