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.
精彩评论