Web Analytics Made Easy -
StatCounter Multi table SELECT FROM with a few restrictions - CodingForum

Announcement

Collapse
No announcement yet.

Multi table SELECT FROM with a few restrictions

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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
    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

    Comment


    • #3
      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.)
      Be yourself. No one else is as qualified.

      Comment

      Working...
      X