SQL dupe problem

I'm having trouble figuring out a tricky sql problem. Any help is greatly appreciated.



Using the following tables:



event

- event_id

- event_type_id

- start_date



event_type

- event_type_id

- event_name



event_category

- event_category_id

- category_name



event_cat_relation

- er_cat_id

- event_id

- event_category_id



To populate an html select box, I'd like to query for event_id and event_name. I can use the following sql to get the data, but duplicates are persistent. Despite numerous variations of the sql, I cannot remove the dupes.



SQL

=============

select e.event_id, t.name

from event e, event_type t, event_cat_relation r

where e.event_type_id = t.event_type_id and e.event_id = r.event_id

order by t.name





RESULTSET

=============

EVENT_ID NAME

1567 Exercise and the Personality

1567 Exercise and the Personality

1567 Exercise and the Personality

1227 Expectant Fathers

1227 Expectant Fathers

1232 Expectant Grandparents

1231 Expectant Grandparents

1579 Health Insurance: Am I Covered?

1579 Health Insurance: Am I Covered?

1213 Health on the Net

1573 Heart Failure Prevention and Treatment

1573 Heart Failure Prevention and Treatment





Thanks for any help.



michael

Hello. Thanks for the help, but the query doesn't do it. I get the same results as my previous query, which include multiple rows of each event.

add:



to the query.

i doubt that very very much



according to your table layouts, each event has one event type



therefore, a many-to-one relationship



note that my query returns each event with its parent event type



there's no way that query can return duplicates



aside to dave: just throwing a GROUP BY into a query is as risky as just throwing a DISTINCT into a query -- it can mask an underlying design problem

Ok, I realize that I've mis-spoken - or haven't provided enough detail. I'm sorry for that.



For this purpose, I consider a dupe to be two rows with the same name value.



The query:



select e.event_id, t.name

from event e

inner

join event_type t

on e.event_type_id = t.event_type_id

order

by t.name



produces the following output, which contains dupes:

EVENT_ID NAME

523 A Baby?…Maybe!

660 A Baby?…Maybe!

661 A Baby?…Maybe!

1302 Alzheimer's Disease and Related Disorders

1304 Alzheimer's Disease and Related Disorders

1303 Alzheimer's Disease and Related Disorders

1637 Annual Awards Dinner

1208 Arthritis and You: Learn How to Take Control

yes, of course they appear to be dupes



you have multiple events of the same type, but i'll bet the event dates are different



that's like saying i have multiple days of the same type (e.g. saturday) in a year, but the dates are different



so getting back to your original problem, which is to populate a dropdown box, you have to figure out which evnt you want to list -- the first one? the last one? the one in a certain category?

It really doesn't matter to me to much which I get. Although I'll capture the event_id, I'm really after the event_type_id. But, a "feature" in the design that requires I record event_id.



This feature allows us to associate any given event with any other sub-category. In the end though, we discovered what we really need is to associate the events at the event_type level.



When reading from the db, the fix is to query for the the event_type_id from the row, using the event_id as a key.

sorry, i'm lost



if you want "to query for the the event_type_id from the row, using the event_id as a key" then why don't you just do that?