Many to Many relationship in MySQL
Posted: Sat Sep 14, 2002 2:05 pm
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.
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.