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
    New Coder
    Join Date
    Dec 2008
    Posts
    58
    Thanks
    2
    Thanked 1 Time in 1 Post

    Multi table SELECT FROM with a few restrictions

    Ok so I dont know if you can do the following, but here is what I want to do:

    I have 2 tables with structures/content sort of like the following:
    Code:
    table1:
    id  |  name
    1   | Jack
    2   | Jack
    3   | Jill
    4   | Adam
    5   | Amy
    
    table2:
    id  | name
    1   | Jack
    2   | Amy
    So here is what I am currently doing:
    Code:
    SELECT * FROM table1, table2 WHERE table1.name=table2.name;
    So this returns 3 rows in essence (from table 1: 1,2,5) And seems to always choose the largest number. What I want it to do is limit this...

    I want to make it so that for each object in table2 I want it to go through table1 and select only the first one (or last one, or middle one... in essence I just want it to pick up one and none more)... so in essence I want the number of elements in table2 to limit the number of total elements selected.

    Let me know if this is possible and how I would do this. I'm fairly new to MySQL so Im trying to learn. Thanks!

  • #2
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    You can use GROUP BY name to limit the results for each unique name. When you do this, you must only select the column specified in the GROUP BY-- in other words, kiss SELECT * goodbye. Also notice I'm using the superior "JOIN ON" syntax. Makes the query easier to read and understand.

    Code:
    SELECT table1.name
    FROM table1
    JOIN table2
    ON table1.name = table2.name
    GROUP BY table1.name

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,076
    Thanks
    80
    Thanked 4,550 Times in 4,514 Posts
    The problem with Fumigator's answer is that you can't get any *OTHER* data from table1 that differs in various rows.

    Now, this might not be a problem, at all, in your example. But let's take something like this:
    Code:
    table1:
    id  |  name | message
    1   | Jack    | I ate a watermelon
    2   | Jack    | I like bananas
    3   | Jill       | Hello, all
    4   | Adam  | Zambonis Rule!
    5   | Amy    | Never mind
    
    table2:
    id  | name
    1   | Jack
    2   | Amy
    You can't use the GROUP BY trick:
    Code:
    SELECT table1.name, table1.message
    FROM table1
    JOIN table2
    ON table1.name = table2.name
    GROUP BY table1.name, table1.message
    You will still get both "Jack" records.

    So what you have to do is something like this:
    Code:
    SELECT T1.name, T1.message
    FROM ( SELECT name, MIN(id) AS minid FROM table1 GROUP BY name ) AS M,
        table1 AS T1,
        table2 AS T2
    WHERE M.name = T2.name
      AND M.minid = T1.id
    Does that make sense?

    Naturally, you can use MIN(id) or MAX(id). (Not sure how you'd easily pick any id except first or last for each name. Ehhh...I guess I do see a way to pick middle one, but it would be complex.)


  •  

    Posting Permissions

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