View Full Version : Multi table SELECT FROM with a few restrictions

04-15-2009, 10:43 PM
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:

id | name
1 | Jack
2 | Jack
3 | Jill
4 | Adam
5 | Amy

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!

04-16-2009, 12:36 AM
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
04-16-2009, 06:05 AM
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:

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

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