Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
04-15-2009, 10:43 PM #1
- Join Date
- Dec 2008
- 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:
table1: id | name 1 | Jack 2 | Jack 3 | Jill 4 | Adam 5 | Amy table2: id | name 1 | Jack 2 | Amy
SELECT * FROM table1, table2 WHERE table1.name=table2.name;
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 #2
- Join Date
- Dec 2005
- Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
- 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
04-16-2009, 06:05 AM #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:
You can't use the GROUP BY trick: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 will still get both "Jack" records.Code:SELECT table1.name, table1.message FROM table1 JOIN table2 ON table1.name = table2.name GROUP BY table1.name, table1.message
So what you have to do is something like this:
Does that make sense?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
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.)