Perform 2 Right Joins In Query

[eluser]rochellecanale[/eluser]
Hello guys I need your help this is not a CI problem but I hope you can help me. I have a 3 tables named (member,downline and points_ledger)

Code:

Here's the structure

mysql> desc member;
+-----------------+-------------------------------------------------------------+------+-----+---------+----------------+
| Field           | Type                                                        | Null | Key | Default | Extra          |
+-----------------+-------------------------------------------------------------+------+-----+---------+----------------+
| member_id       | int(8) unsigned                                             | NO   | PRI | NULL    | auto_increment |
| account_type    | enum('REGULAR','DUPLICATE','CORPORATE','ADVANCE','WALK-IN') | NO   |     | NULL    |                |
| username        | varchar(125)                                                | NO   | UNI | NULL    |                |
| lastname        | varchar(50)                                                 | NO   |     | NULL    |                |
| firstname       | varchar(50)                                                 | NO   |     | NULL    |                |
| middlename      | varchar(50)                                                 | NO   |     | NULL    |                |
| gender          | enum('Male','Female')                                       | NO   |     | NULL    |                |
| address         | varchar(100)                                                | NO   |     | NULL    |                |
| birthday        | date                                                        | NO   |     | NULL    |                |
| email           | varchar(50)                                                 | NO   | UNI | NULL    |                |
| mobile_number   | varchar(50)                                                 | NO   | UNI | NULL    |                |
| password        | varchar(50)                                                 | NO   |     | NULL    |                |
| tin             | varchar(50)                                                 | NO   |     | NULL    |                |
| upline          | varchar(15)                                                 | NO   |     | NULL    |                |
| account_created | datetime                                                    | YES  |     | NULL    |                |
+-----------------+-------------------------------------------------------------+------+-----+---------+----------------+
15 rows in set (0.00 sec)

mysql> desc downline;
+----------+------------------+------+-----+---------+----------------+
| Field    | Type             | Null | Key | Default | Extra          |
+----------+------------------+------+-----+---------+----------------+
| down_id  | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| level    | int(10) unsigned | NO   |     | NULL    |                |
| fkmember | int(8) unsigned  | NO   | MUL | NULL    |                |
| downline | int(8) unsigned  | NO   |     | NULL    |                |
+----------+------------------+------+-----+---------+----------------+
4 rows in set (0.02 sec)

mysql> desc  points_ledger;
+-----------------+-------------------------------------------------+------+-----+---------+----------------+
| Field           | Type                                            | Null | Key | Default | Extra          |
+-----------------+-------------------------------------------------+------+-----+---------+----------------+
| ledger_id       | int(10) unsigned                                | NO   | PRI | NULL    | auto_increment |
| completion_date | datetime                                        | YES  |     | NULL    |                |
| completion_day  | enum('Mon','Tue','Wed','Thu','Fri','Sat','Sun') | NO   |     | NULL    |                |
| type            | enum('EARNED','DIRECT DEBIT','PAYOUT')          | YES  |     | NULL    |                |
| amount          | int(10)                                         | NO   |     | NULL    |                |
| running_balance | float(10,2) unsigned                            | NO   |     | NULL    |                |
| fkmember        | int(8) unsigned                                 | NO   | MUL | NULL    |                |
+-----------------+-------------------------------------------------+------+-----+---------+----------------+
7 rows in set (0.02 sec)

All i want is to extract the following fields to create one table

ledger_id, (from points_ledger)
username, (from member)
lastname, (from member)
firstname, (from member)
middlename, (from member)
completion_date, (from points_ledger)
type, (from points_ledger)
sum(amount) as amount, (from points_ledger)
running_balance, (from points_ledger)
fkmember, (from points_ledger)
level (from downline)

My problem is i can only join two queries the member and the points_ledger but when i add the downline table it results to ambiguous fkmember. How can i create a join query? Hope you can help me.

[eluser]adamck[/eluser]
To save some time, could you send me an export of some example data with the table structure in a .sql file.
OR export the test data to a .sql and paste the .sql contents to a pastie or any other way, its hard to create a complex query without the test data to play with
This way i can import it to phpmyadmin and test some queries on it.
Im fairly sure i have a solution, but i need to test it
Ad.

[eluser]rochellecanale[/eluser]
ok i attach my dataabse

[eluser]adamck[/eluser]
Hmm the forum download wont work, just gives me a blank page...

Can you email it me

[email protected]

[eluser]rochellecanale[/eluser]
just tell me if you received it

[eluser]adamck[/eluser]
Yes thanks.
Ill have a look now.

Do you want the query to give a single row result based on an ID?
Or do you want all the rows from the member and points_ledger and then the level from downline if it exists?

Adam.

[eluser]rochellecanale[/eluser]
The second one. Thanks Adam for answering my problem. Just give me a sample query so that i can test too. Ok got to go. Thanks again.

[eluser]adamck[/eluser]
Hmm something like this?

Code:

SELECT
ledger_id,
username,
lastname,
firstname,
middlename,
completion_date,
type,
sum(amount) as amount,
sum(running_balance) as running_balance,
points_ledger.fkmember,
downline.level
FROM member
LEFT OUTER JOIN points_ledger ON points_ledger.fkmember = member.member_id
LEFT OUTER JOIN downline ON downline.fkmember = points_ledger.fkmember
GROUP BY fkmember

[eluser]rochellecanale[/eluser]
It results to:

Code:

+-----------+----------------+----------------+-----------------+------------------+---------------------+--------+--------+-----------------+----------+-------+
| ledger_id | username       | lastname       | firstname       | middlename       | completion_date     | type   | amount | running_balance | fkmember | level |
+-----------+----------------+----------------+-----------------+------------------+---------------------+--------+--------+-----------------+----------+-------+
|      NULL | WALK IN USER   | N/A            | N/A             | N/A              | NULL                | NULL   |   NULL |            NULL |     NULL |  NULL |
|        16 | testusername0  | Testlastname   | Testfirstname   | Testmiddlename   | 2012-11-01 03:01:37 | EARNED |      0 |           65.00 |        2 |     1 |
|         6 | ssssssssssss   | Sssssssss      | Sssssssss       | Sssssssss        | 2012-10-03 04:41:53 | EARNED |      0 |           35.00 |       37 |     1 |
|        18 | admintest      | Admin          | Admin           | Admin            | 2012-11-15 04:14:31 | EARNED |      0 |           30.00 |       38 |     1 |
|         7 | canaleryc      | Canale         | Rochelle        | Yang             | 2012-10-05 03:27:28 | EARNED |      0 |           55.00 |       47 |     1 |
|         8 | adminlocalhost | Canale         | Rochelle        | Yang             | 2012-10-05 05:57:45 | EARNED |      0 |           25.00 |       53 |     1 |
|        15 | junteniola     | Teniola        | Fernando Jr.    | B.               | 2012-10-25 04:15:35 | EARNED |      0 |           25.00 |       63 |     1 |
|        14 | hilsoft001     | Hilsoft        | Hilsoft         | Htlsoft          | 2012-10-23 08:29:28 | EARNED |      0 |           30.00 |       64 |     1 |
|        17 | dummy_user     | Dummy_lastname | Dummy_firstname | Dummy_middlename | 2012-11-15 05:35:53 | EARNED |      0 |           25.00 |       76 |     1 |
+-----------+----------------+----------------+-----------------+------------------+---------------------+--------+--------+-----------------+----------+-------+

yeah that's what i need but the running balance is incorrect it should be the sum of one account for example i have an account. All I need is to get to know the sum of my running balance. Take a look at member 'ssssssssssss' the sum of his account should be 5.00 and the sum of running balance is 5.00 also. Thanks for the answer it will really help me a lot. Keep up the good work my friend.

[eluser]rochellecanale[/eluser]