CodeIgniter Forums
Help me writing a query - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Help me writing a query (/showthread.php?tid=21626)

Help me writing a query - El Forum - 08-16-2009

[eluser]aryan_[/eluser]
I have a users table like this:

Code:
-----------------------
id | email | referrer  
-----------------------
1  | [email protected] |          
-----------------------
2  | [email protected] |    1    
-----------------------
3  | [email protected] |    1
-----------------------
4  | [email protected] |    2
-----------------------
referrer is user id of another user who referred him to this site.

and an ad_watch table like this:

Code:
-----------------------
id | user | datetime
-----------------------
1  | 2    | 1249142107
-----------------------
2  | 3    | 1249142107
-----------------------
3  | 4    | 1249142107
-----------------------

I want get data of all users referred by a user. e.g. Retrieve all users in ad_watch table(here user 2 and 3) where user is referred by user 1 in users table.

I'm unable to explain it properly, but you guys can understand what I need.

Thanks much!

Help me writing a query - El Forum - 08-16-2009

[eluser]mohamedhelal[/eluser]
Iam sorry bocuse my english bad
try this I wach this you want



Code:
SELECT*FROM users,user where user.referrer=users.id


Help me writing a query - El Forum - 08-16-2009

[eluser]aryan_[/eluser]
Resolved myself:

SELECT ad_watch.id, users.id FROM ad_watch LEFT JOIN users ON ad_watch.user_id=users.id WHERE users.refferer={user_id}


Thanks!

Help me writing a query - El Forum - 08-16-2009

[eluser]mohamedhelal[/eluser]
try this

Code:
SELECT * FROM ad_watch,users where ad_watch.user_id=users.id and users.refferer=user_id
if is working replace this * be this


Code:
ad_watch.user_id,users.id ,users.refferer,user_id


to by like this

Code:
SELECT
ad_watch.user_id,users.id ,users.refferer,user_id
FROM
ad_watch,users where ad_watch.user_id=users.id and users.refferer=user_id


an sha alah
his working