Your problem is that you are doing a join of two tables but you haven't stated the joining condition (what the two tables have in common). When you join tables without stating a joining condition, every row of the first table joins with each row of the second table. This is called a Cartesian Product and is usually a very undesireable result.
To solve your problem, you need to identify exactly what the two tables have in common. They do have something in common, right? You haven't stated explicitly what columns are in each of the tables but, if you've normalized your data correctly, you should have gotten something like this: Venues ===== VenueID VenueName VenueLocation primary key = VenueID Events ==== EventID EventName EventLocation VenueID [**VERY IMPORTANT**] EventDate primary key = EventID foreign key = VenueID (refers back to VenueID of Venues table) Then, your query should look like this: select e.eventID, date_format(e.EventDate, '%c/%d/%y') as EventDate, e.EventTime, v.VenueName, v.VenueID from events e, venues v where e.VenueID = v.VenueID; This tells MySQL to join each row in the Events table to only the one row in the Venues table that has the same VenueID on it. That should eliminate your duplicate rows. Rhino ----- Original Message ----- From: "Erich Beyrent" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Thursday, March 04, 2004 10:23 AM Subject: Duplicates returns in query? > Hi all, > > I have a problem with a select returning duplicates, even though there > aren't any duplicates in the database. > > select > e.EventID, > date_format(e.EventDate, '%c/%d/%y') as EventDate, > e.EventTime, > v.VenueName, > v.VenueID > from > events e, > venues v; > > > +---------+-----------+-----------+-----------------+---------+ > | EventID | EventDate | EventTime | VenueName | VenueID | > +---------+-----------+-----------+-----------------+---------+ > | 2 | 3/06/04 | 09:00:00 | The Bombshelter | 1 | > | 3 | 3/12/04 | 09:00:00 | The Bombshelter | 1 | > | 2 | 3/06/04 | 09:00:00 | Goodtimes | 2 | > | 3 | 3/12/04 | 09:00:00 | Goodtimes | 2 | > +---------+-----------+-----------+-----------------+---------+ > > I tried adding a "distinct" to the above select, which had no effect. I > also ran an "explain" on the query: > > +-------+------+---------------+------+---------+------+------+-------+ > | table | type | possible_keys | key | key_len | ref | rows | Extra | > +-------+------+---------------+------+---------+------+------+-------+ > | e | ALL | NULL | NULL | NULL | NULL | 2 | | > | v | ALL | NULL | NULL | NULL | NULL | 2 | | > +-------+------+---------------+------+---------+------+------+-------+ > > Which tells me nothing. What have I done wrong here? > > -Erich- > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
