[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.
