I'm working on a small app, which receives a list of 20 players in XML format.
The initial version works ok and I use there just 1 SQL statement and thus it is easy for me to fetch results row by row and print XML at the same time:
select u.id, u.first_name, u.city, u.avatar, m.money, u.login > u.logout as online from pref_users u, pref_money m where
m.yw=to_char(current_timestamp, 'YYYY-IW') and u.id=m.id order by m.money desc limit 20 offset ?
My problem is however, that I need to add more data for each user representing their statistics over the last 20 weeks. And that data is in separate tables: pref_money, pref_pass, pref_game:
# select yw, money from pref_money where id='OK122471020773' order by yw desc limit 20; yw | money ---------+------- 2010-52 | 760 2010-51 | 3848 2010-50 | 4238 2010-49 | 2494 2010-48 | 936 2010-47 | 3453 2010-46 | 3923 2010-45 | 1110 2010-44 | 185 (9 rows)
so that I can take that attribute in my app and use it in a chart.
My problem is that I don't know how to bring this together in 1 SQL statement (i.e. the SQL statement at the top and then the concatenated 20 rows from 3 tables).
Is it possible? Maybe I need to write a PgPlSQL procedure for each of the 3 tables and then add them to the SQL statement above? But how do I concatenate the rows, should I create a PgPlSQL variable and always append values to it in a loop or is there a better way?
