Hello,
Firstly, here is a basic ERD of what I am working on:
http://img262.imageshack.us/img262/3562/erd1ug8.png
poker players play in multiple rooms, each having their own UID. They are linked by their wsop_id. If a player plays in 3 rooms, then 3 players will have the same wsop_id.
This query returns rows with the WSOP_id, the poker_rooms_members_id, and the sum(rake) for that poker_rooms_members_id.
Code:
SELECT wsop_id, poker_rooms_members_UID, poker_room_id, sum( rake )
FROM (
SELECT wsop_id, poker_rooms_members_UID, poker_rooms_members.poker_room_ID, date, rake
FROM poker_rooms_rake
LEFT JOIN poker_rooms_members ON poker_rooms_rake.poker_rooms_members_UID = poker_rooms_members.UID
LEFT JOIN wsop_members ON poker_rooms_members.wsop_member_id = wsop_members.wsop_id
) AS d
GROUP BY poker_rooms_members_UID
ORDER BY wsop_id
However, if the parent wsop_id has 2 children (poker_rooms_members_UID) that play on 3 'seperate poker_rooms_IDs', then I will see 3 rows, with 3 rake totals, before moving onto the next wsop_id.
What I would like to do is create 3 derived columns for each of the poker_room_IDs. So my result set will have the columns:
wsop_id | poker_room_1_rake | poker_room_2_rake | poker_room_3_rake
I'd greatly appreciate anyone who can point be in the right direction!
Cheers
dBforums
>
Derived Columns
03-15-07, 14:11
Derived Columns
Linear Mode
