CODEWITHSUNDEEP
mysqljoin
javment
javment

Reputation: 368

Mysql join get one row per match

I have a mysql query with a join and I want for each match to get one only row not all. What I mean.

for example we have the two tables 

ids(int id)
colors(int id, varchar color)

I have the query 

select ids.id, colors.color from ids join colors on ids.id = colors.id;

and get the following

id | color
------------------
1  | red 
1  | blue 
2  | yellow 
3  | green 
3  | pink

I want a query to get the 

id | color 
-----------------
1  | red 
2  | yellow 
3  | green

Upvotes: 2

Views: 3297

Answers (5)

Alexis
Alexis

Reputation: 66

Try using a subquery which limits the amount of results:

select ids.id, (select colors.color WHERE colors.id = ids.id LIMIT 1) AS color FROM ids;

Upvotes: 4

javment
javment

Reputation: 368

Thank you for your replies, all of you said me to use the group by Of course this method was the first that I tried, but I get the below error:

Column "COLORS.COLOR" must be in the GROUP BY list; SQL statement:

My query is the 

SELECT 
    ids.id, colors.color 
FROM ids 
     JOIN colors ON ids.id = colors.id 
GROUP BY ids.id;

Also, I must say you that I use the h2database

Upvotes: 0

nobody
nobody

Reputation: 10645

SELECT ids.id, colors.color FROM ids JOIN colors ON ids.id = colors.id GROUP BY ids.id

Upvotes: 0

Vlad
Vlad

Reputation: 10780

select ids.id, colors.color from ids join colors on ids.id = colors.id group by ids.id;

But remember: you get an unguaranteed value for colors.color.

Upvotes: 0

heximal
heximal

Reputation: 10517

select 
  ids.id, 
  min(colors.color) as color
from 
  ids 
join 
  colors on ids.id = colors.id
group by
  ids.id
;

Upvotes: 3

Related Questions