Many to Many relationship in MySQL

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
EricS
Forum Contributor
Posts: 183
Joined: Thu Jul 11, 2002 12:02 am
Location: Atlanta, Ga

Many to Many relationship in MySQL

Post 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.
User avatar
hob_goblin
Forum Regular
Posts: 978
Joined: Sun Apr 28, 2002 9:53 pm
Contact:

Post 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.
Post Reply