Multi table SELECT FROM with a few restrictions

[thecaligarmo]

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:

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:

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!

[Fumigator]

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.

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

[Old Pedant]

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:

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:

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:

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