Page 1 of 1

Many to Many relationship in MySQL

Posted: Sat Sep 14, 2002 2:05 pm
by EricS
Using Mysql 3.23.37

I have the following tables created.

sourceinfo:
sourceid
sourcename

locationinfo:
locationid
locationname

countdata:
sourceid
locationid
visitorcount
date

Hopefully it's easy to see where I'm going with the tables. I set up new sources and locations in the first two tables and the countdata table keeps a count of how many people visit each source/location combination on any given date.

Now what I want to do is search the database and get a list of all the records for each location&source combination found in the countdata. But the record will have the names as well as the id's.

But I don't want to see duplicate source/location records, which come in because the counts are kept on a day by day basis. So locationid 1 sourceid 1 could be in the countdata 10 times representing 10 different days. I just want them listed once for each combination.

Also, I don't want listings of sourceid/locationid combinations that haven't been recorded in the countdata table.

It's a little long but I wanted to get you all the information you might need.

I'm pretty sure that left joining once or twice will solve the issue, but I can't get it work quite right.

Thanks for your time.

Posted: Sat Sep 14, 2002 2:41 pm
by hob_goblin
if the count thing has sourceid in it, you wouldn't have to worry about having unique records

you'd just do SELECT * FROM countdata WHERE sourceid = '#'

but some other things you might want to look into are the 'unique' field types, or selecting COUNT, like SELECT count(blah) FROM blah, or something like that.