inner join help

[digitalgod]

hey guys,

 

I have 2 tables, "affiliates" and "affiliatesreferals". In the first table there's all the information for the affiliates, such as account information, username,password, becameaffiliate (date they joined) etc and in the second table you have userid (the referee) and refid (the referer)

 

so I want to be able to make a query that shows all the people an affiliate refered and show some basic info like their names, username etc and order it by when the referee became a user. Makes sense??

 

this is what I have so far, the .......... is where I'm not sure what to do....

 

SELECT userid,refid, affiliates.firstname, affiliates.lastname, affiliates.becameaffiliate
FROM (affiliatesreferals inner join affiliates on .........)
WHERE affiliates.id=".$_GET["id"]."  //id of the referer
ORDER BY becameaffiliate; //date the referee joined

 

any ideas??

[btherl]

When joining back to the same table, you need to give each copy an alias:

 

SELECT ...
FROM affiliates a
JOIN affiliatesreferals ar ON (a.userid = ar.userid)
JOIN affiliates r ON (ar.refid = r.userid)

 

Now "r", which was joined to the refid, contains data for the referers.  "a", which was joined to userid, contains the referees' data.  For example, if r.username and a.username are in the same row, then r.username referred a.username

[digitalgod]

ok I'm not sure if I understand perfectly...

 

so if I want to echo r.username how do I go about doing that?

 

something like this ?

 

while ($therecord=mysql_fetch_array($queryresult)){
echo $therecord['r.username'] ??

 

also I'm not sure if it's working properly but this is what I have so far

 

SELECT userid, refid, affiliates.refid, affiliates.firstname, affiliates.lastname, affiliates.becameaffiliate
FROM affiliates a
JOIN affiliatesreferals ar ON ( a.userid = ar.userid ) 
JOIN affiliates r ON ( ar.refid = r.userid ) 
WHERE r.refid = ".$_GET['id']."
ORDER BY r.becameaffiliate

 

phpmyadmin gives me Column: 'refid' in field list is ambiguous

 

[btherl]

Hmm.. i've never requested data using the table name like that, but you can certainly do it by aliasing a column.  For example, using your query in your latest post:

 

SELECT r.userid AS ref_userid, ar.refid AS ar_refid,
a.refid AS a_refid, a.firstname, a.lastname, a.becameaffiliate
FROM affiliates a
JOIN affiliatesreferals ar ON ( a.userid = ar.userid ) 
JOIN affiliates r ON ( ar.refid = r.userid ) 
WHERE r.refid = ".$_GET['id']."
ORDER BY r.becameaffiliate

 

In this case you can fetch ref_userid, refid, ar_refid, a_refid, firstname, lastname, becameaffiliate

 

The columns which are unique can be fetched by name alone.  But when they aren't unique, I usually use an alias.  I'm not sure if there's other ways to do it.

[digitalgod]

thanks btherl,

 

I think I complicated things for absolutly nothing. All I did was 2 queries, one that get the info for the referer (using $_GET) and the other gets the info for the referee by using $_GET['id'] and refid.

 

I really don't know why I complicated things for nothing, I guess I was planning on doing something totally different at first.

 

thanks for your help!