Self Join?

[jarrren]

I have a table with event_id, Genre_id, Venue_id, timestamp_. If a band is playing at the same place more then once they will be assigned a new event_id, but the genre_id and venue_id will stay the same.

Ex:
Event_id, Genre_id, Venue_id, timestamp_
Radio Head(100), 125, 50, 1/25/06
Radio Head(101), 125, 50, 1/26/06
Radio Head(102), 125, 50, 1/27/06
Radio Head(103), 125, 50, 1/28/06

How do I just pull an event once if their playing at the same Venue several times?

There are several events with the same venues in my database.

Thanks

[chazzy]

so the only thing that changes are the dates? you could do a distinct select to find the event_id, genre_id and venue_id, then a query against those 3 to find all dates.

i dont' think you'll beable to generate an infinite list of dates using queries alone. probably with a stored procedure, but not a query.

[jarrren]

No, The Event_Id changes for each date as well. What I really want to do is only pull one event for each similar (genre_id and venue_id)

Ex: Say an event has the following values for Genre_id, Venue_id - 125,50.

Then I don't want any other events to be pulled that have those values for Genre_id and Venue_id.

So a self join wasn't the solution?

[NightShift58]

Will this do it for you?SELECT * FROM `event_table`
GROUP BY `genre_id`, `venue_id`
ORDER BY `event_id` ASC

[jarrren]

I get a 'not a GROUP BY expression ' error with that code.

Does this query sound impossible to do with this one table? Would a union query help?

[NightShift58]

Capitalization... sorry, should be:SELECT * FROM `event_table`
GROUP BY `Genre_id`, `Venue_id`
ORDER BY `Event_id` ASCBased on your description of the needed data, I think that this is all you need, unless I misread the question...