Counting instances in tables

krotkruton

My problem isn't as simple as the title suggests, but it goes along those lines.

I have two tables, userdestinations and barlist. Userdestinations has 5 fields, email, first_bar, second_bar, third_bar, and date, where each bar field contains an ID of a bar and there aren't any duplicate bar ID's for a given email and date (there are multiple duplicate emails in the list but no two duplicate emails have the same date). Barlist has a lot of information but the important information is that it has a bar id and city.

Given a city and date, I want to have a count on how many users have each bar as either their first, second, or third bar. This information will be displayed in a table along with some more general information about each bar that I get from a simple "SELECT * FROM barlist WHERE city = '$this_city'".

Since this list will be fairly long (maybe 30 rows or so), it seems rather innefficient to get that number by creating a for loop or something that checks each ID from the previous generic search, so I'm hoping that there is some function that I don't know much about that might be able to simplify and streamline this. I tried using Joins, but couldn't find anything very useful. Any suggestions would be appreciated.

tomhath

Try something like this (not completely tested):

select b.barid, 
  count(t1.first_bar) as 'first_bar_count', 
  count(t2.second_bar) as 'second_bar_count', 
  count(t3.third_bar) as 'third_bar_count' 
from barlist b
left join userdestinations t1
on b.barid = t1.first_bar and t1.date = '$this_date'
left join userdestinations t2
on b.barid = t2.second_bar and t2.date = '$this_date'
left join userdestinations t3
on b.barid = t3.third_bar and t3.date = '$this_date'
where b.city = '$this_city'    

group by b.barid

krotkruton

Ok, I have to ask a few questions before I try that because there is a lot of stuff there that is new to me.

When you do "...from barlist b" you are actually creating a variable, b, which is a barlist? That way you can do the "select b.barid" that should really give you the barid from the results from barlist b.

I've done some joins, but not like that. Could you explain "left join userdestinations t1 on b.barid = t1.first_bar and t1.date = '$this_date' " line. Particularly, the "left" part is completely new to me.

Finally, the "count(t1.first_bar) as 'first_bar_count'" line means that afterwards I can call row_query['first_bar_count'] to get the number of times the bar pops up as first_bar? Also, I couldn't find much information, other than error messages and troubleshooting, about the count function on mysql.net, so I really don't know what count(t1.first_bar) does, or what count* does or pretty much anything else involving count, and I also can't seem to find count on mysql.com.

As a side note, it looks to me like this will have 3 separate counts: one for when the bar is is either in the first, second, or third position. If it could make the code simpler, it doesn't matter to me which position it is in and for each person, the bar will only appear once in one fo the positions (a person is not allowed to have the same bar more than once). Since a person isn't allowed to have the same bar more than once, I could also add the 'first_bar_count' to the other two counts and get the same results, but I thought I would mention this in case it makes things easier.

Anyway, I didn't mean to sound ungrateful because this is a big help, but I'm still kinda far away from understanding it. Regardless, thanks for the great help, but if you could clarify, that would be great.