Tricky query !? Merge three tables!

[tamilsweet]

Hi All,
I'm trying to write a mysql query to fetch details in certain format with no luck yet!

I have tables
user (id INT, user_name INT);
1, user1
2, user2
....
x, userx

site (id INT, site_name INT);
1, site1
2, site2
.....
y, sitey

stats(id INT, user_id INT, site_id INT, count);
1, 1, 1, 100
2, 1, 2, 150
3, 2, 1, 125
4, 2, 2, 175
.....
....
x*y, x, y, <count>


I need output in the format
-------site1/count--site2/count--......sitey/count
user1 100 150
user2 125 175
....
userx

i hope my requirement is clear!?

[tosbourn]

You are going to need to use multi-dimensional arrays for this.

First solve it assume there is only ever one user, you should find this simple.

Then expand on that to solve for x users (hint: grab all the users first, then make a for loop to cycle through all users)

[oracleguy]

To build a query with a result set like that with a dynamic number of sites, I would recommend writing a procedure to do it instead. That way it would be much more flexible to handle the dynamic number of columns.

Or if you don't want to do that, return the data a little differently and then just do a little post processing on the application side so you can present it in the way that you want. A query that would let you do that would be like:


SELECT site_name, user_name, stats.count
FROM stats
JOIN user ON stats.user_id = user.id
JOIN site ON stats.site_id = site.id
ORDER BY user_name, site_name