Creating A Report with JOINS in MySQL

[QRator]

Morning guys

I need to generate a report that can will pull data from a number of tables and combine them.
The table structures are as follows:

Table 1: picture_stats

id | user_id | picture_id | event_id | venue_id | venuetype_id | region_id | time | date |ip

Table 2: site_users

id | username | cellphone

Table 3: venues

venue_id | venue_name | venue_pic | venue_info | address | venuetype_id | region_id | web | tel

Basically what I need is to create a report that will show the venues that a specific cellphone number has visited. ie. The report I want to generate must look like this ...

Cellnumber: 0832717341

VENUE LIST:

venue_name1 - [number of visits]
venue_name2 - [number of visits]
venue_name3 - [number of visits]
venue_name4 - [number of visits]
venue_name5 - [number of visits]
venue_name6 - [number of visits]

The venues above must be listed by number of visits from high to low.

Hope this is clear. I know that one would need to use INNER JOINS etc. to accomplish this. But I am battling with it.

Your help is appreciated.

[kevinc]

select venues.venue_name,site_users.cellphone,count(*)  as hits from picture_stats 
inner join venues on picture_stats.venue_id=venues.venue_id
inner join site_users on picture_stats.user_id=site_users.id
where LEFT(site_users.cellphone,3)='083'
group by picture_stats.venue_id
order by hits desc

There you go Justin. 😛