CodeIgniter Forums
Right Join Query Problem - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forum-20.html)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forum-23.html)
+--- Thread: Right Join Query Problem (/thread-55966.html)



Right Join Query Problem - El Forum - 11-19-2012

[eluser]rochellecanale[/eluser]
Hello guys actually it is not a CI problem. But I hope you can help me.
My problem is all about query. I tried all my best but i give up. Here's my tables

Code:
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)

My goal is to get all the downline member of a certain user using the fields member_id(from table member), username(from table member) level (from downline member).

Example:
When i click the button Account History it list all the transactions together the earned points(used in virtual wallet) and payouts (income). And if i click the EARNED it drilldown all my member downline. An it redirect to other page showing my member in a specified level.



Right Join Query Problem - El Forum - 11-19-2012

[eluser]solid9[/eluser]
This is not hard, this is a One to Many relationship tables.
You need to convert your DownLine table from this,
Code:
+----------+------------------+------+-----+---------+----------------+
| 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    |                |
+----------+------------------+------+-----+---------+----------------+

to this table,
Code:
+----------+------------------+------+-----+---------+----------------+
| Field    | Type             | Null | Key | Default | Extra          |
+----------+------------------+------+-----+---------+----------------+
| down_id  | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| member_id| int(8) unsigned  | NO   | IDX | NULL    |                |                
| level    | int(10) unsigned | NO   |     | NULL    |                |
| downline | int(8) unsigned  | NO   |     | NULL    |                |
+----------+------------------+------+-----+---------+----------------+

Noticed the newly added member_id column.

I don't think you need the fkmember column?
All you need is the member_id from member table and copy that into DownLine table.

The rest are easy.






Right Join Query Problem - El Forum - 11-19-2012

[eluser]solid9[/eluser]
The question is how do you compute earned points(used in virtual wallet) and payouts (income)?



Right Join Query Problem - El Forum - 11-19-2012

[eluser]rochellecanale[/eluser]
I have a specifc code for that every level it required an amount of recruited member. Thanks for the answers