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.
Many to Many relationship in MySQL
Moderator: General Moderators
- hob_goblin
- Forum Regular
- Posts: 978
- Joined: Sun Apr 28, 2002 9:53 pm
- Contact:
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.
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.