mysql sql distinct with join two table

Question

I have two mysql table , table event and table eventvenue.

I want to get other event from a venue.

--------------table event:---------------

id_e
id_event
title
content

-------------- table eventvenue:--------------

id_ev
id_event_ev
id_location_ev

I use join to get other event from a venue:

SELECT * 
FROM eventvenue 
JOIN event ON eventvenue.id_event_ev = event.id_event 
WHERE eventvenue.id_location_ev=1

The result is:

id_ev  id_event_ev  id_location_ev  id_e  id_event  title   content  
-----  -----------  --------------  ----  --------  ------  ------------
1      2            1               2     2         eventA  aaaaaaaaaaaa
2      2            1               2     2         eventA  aaaaaaaaaaaa
1      4            1               4     4         eventB  bbbbbbbbb
1      9            1               9     9         eventC  cccccccc
3      5            1               5     5         event5  555555

The output contains two rows with the same id_event_ev/id_event value of 2.

How can I show only distinct event IDs per location? Like this:

id_ev  id_event_ev  id_location_ev  id_e  id_event  title   content  
-----  -----------  --------------  ----  --------  ------  ------------
1      2            1               2     2         eventA  aaaaaaaaaaaa
1      4            1               4     4         eventB  bbbbbbbbb
1      9            1               9     9         eventC  cccccccc
3      5            1               5     5         event5  555555

Note: id_ev is not important.

AKZap · Accepted Answer · 2012-05-14 10:20:05Z

2

try this code

SELECT * 
FROM eventvenue 
JOIN event ON eventvenue.id_event_ev = event.id_event 
WHERE eventvenue.id_location_ev=1
GROUP BY event.id_event, eventvenue.id_event_ev

answered May 14, 2012 at 10:20

AKZap

1,2116 gold badges18 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Eugen Rieck · Accepted Answer · 2012-05-14 09:49:14Z

0

In fact you simply want to either ignore id_ev, or get the lowest value of it.

This makes you need

SELECT DISTINCT

-- maybe drop next line
MIN(id_ev) AS id_ev, 
-- maybe drop next line

id_event_ev, id_location_ev, id_e, id_event, title, content 

FROM eventvenue 
INNER JOIN event ON eventvenue.id_event_ev = event.id_event 
WHERE eventvenue.id_location_ev=1

-- Drop next line if you dropped line above
GROUP BY eventvenue.id_ev

answered May 14, 2012 at 9:49

Eugen Rieck

65.6k10 gold badges75 silver badges97 bronze badges

1 Comment

hendra Over a year ago

i try this code, it's work too :D nice idea "get lowest value" :D

Collectives™ on Stack Overflow

mysql sql distinct with join two table

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related