开发者

How to join to tables with certain conditions

I have two tables: accounts and authorizations.

Accounts Table has the full name and id of both customers and agents. The authorizations table has the id of the customer a开发者_如何学Pythonnd agent who is under that customer. The accounts table has the Full Name, and id of both the agent and the customer.

I keep getting the agent name instead of the customer name. not sure how to fix this!

Update: I was able to get the right id but still the name of the customer doesn't show up.

Code:

//Query
$sql = "SELECT authorizations.customer_id, accounts.fullname
        FROM authorizations
        INNER JOIN accounts ON
        authorizations.system_id = accounts.authorizating_customer
        WHERE authorizations.agent_id = '$did'";

$result=mysql_query($query);
$num=mysql_numrows($result);
mysql_close();
echo "";
$i=0;
while ($i < $num) {
    $name = mysql_result($result, $i, "fullname");
    echo "";
    echo "";
    $i++;
}


try

$sql = "SELECT 
            authorizations.customer_id, 
            accounts.fullname 
        FROM 
            authorizations 
                LEFT JOIN 
                    accounts 
                ON 
                    accounts.authorizing_person = authorizations.customer_id 
        WHERE 
            authorizations.agent_id = '$agentid' 
        ORDER BY 
            accounts.id DESC";

$result = mysql_query($query);

$num = mysql_numrows($result);

mysql_close();

echo "";

$i=0;
while ($i < $num) {

    $name = mysql_result($result,$i,"fullname");
    echo "";
    echo "";
    $i++;
}


I don't know the structure of your tables, but you can follow this pseudo code

SELECT name_of_person FROM accounts WHERE authorizing_person = (SELECT authorizing_person FROM authorization WHERE agent_id = '$agentid'"


I got it. Just had to use the customer_id instead of authorizing_customer.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜