如何使用特定值加入两个mysql表？

Question

I have two table one is user and another is tran

Table User

    id      | userName      | email         |Privacy
------------+---------------+---------------+---------------
0           | user1         | [e-mail]   |0
25          | user2         | [e-mail]  |0
150         | user3         | [e-mail]   |1

Table tran

Date     |sender|  amount      | receiver |
---------+------+--------------+----------+
1/1/2013 |user1 |  0           | user2    |
1/2/2013 |user1 |  25          | user3    |
1/2/2013 |user3 |  150         | user1    |

How to join two table for user1 so that i can get the following table(I will set the receiver address according to privacy setting if privacy is 1 then i will show email otherwise i will show the username and type will be set according to the sender and receiver address if user1 is sender then type is transfer otherwise type is received .I don't want this two condition in query because i will be able to set this after query by simply checking the value of the row using php if else condition )

Date     |  amount      |      type       |address
---------+--------------+-----------------+---------------
1/1/2013 |   0          |  transfer       |user2       
1/2/2013 |  25          |  transfer       |[e-mail]   
1/2/2013 |  150         |  received       |user1

I would like to explain a little bit that why i need the join operation.I can fetch the transaction info of user 1 by this way

 select * from transaction where sender='user1' or receiver='user1'  

After fetching the info i will be able to prepare my transaction type in the table based on user1 is the sender or not using just php if else condition .But the problem is i want to mask the username according to the privacy setting of user table so i need to check the that the privacy of the corresponding user is one or not .So i want to join the table user and tran this way so that after fetching data i can just check privacy of the user.

Actually i am working with angularjs and running query like this:

    $query="SELECT  t.`tran_id`,t.`tran_type`,t.`sender`,t.`fee`,t.`date`, t.amount, COALESCE(u.email, t.receiver) AS receiver
    FROM `transaction` t 
       LEFT JOIN `user` u 
       ON u.username = t.receiver
       AND u.verify_email = 0;

    "; 

    $result = $mysqli->query($query) or die($mysqli->error.__LINE__);

    $arr = array();
    if($result->num_rows > 0) {
        while($row = $result->fetch_assoc()) {
            $arr[] = $row;  
        }
    }
    # JSON-encode the response
    $json_response = json_encode($arr);

// # Return the response
echo $json_response;
?>

And output is

<div class="row">
        <div class="col-md-12" ng-show="filteredItems > 0">
            <table class="table table-striped table-bordered">

            <thead>
            <th>Transaction_number&nbsp;<a ng-click="sort_by('tran_id');"><i class="glyphicon glyphicon-sort"></i></a></th>
            <th>Type&nbsp;<a ng-click="sort_by('tran_type');"><i class="glyphicon glyphicon-sort"></i></a></th>
            <th>Address&nbsp;<a ng-click="sort_by('sender');"><i class="glyphicon glyphicon-sort"></i></a></th>
            <th>Amount&nbsp;<a ng-click="sort_by('amount');"><i class="glyphicon glyphicon-sort"></i></a></th>
            <th>Feee&nbsp;<a ng-click="sort_by('fee');"><i class="glyphicon glyphicon-sort"></i></a></th>

            <th>Date&nbsp;<a ng-click="sort_by('date');"><i class="glyphicon glyphicon-sort"></i></a></th>
            <th>Status&nbsp;<a ng-click="sort_by('status');"><i class="glyphicon glyphicon-sort"></i></a></th>
            </thead>

            <tbody>

 <tr ng-repeat="data in filtered = (list | filter:search | orderBy : predicate :reverse) | startFrom:(currentPage-1)*entryLimit | limitTo:entryLimit">
                    <td>{{data.tran_id}}</td>
                    <td>{{data.tran_type}}</td>
                    <td>{{data.sender}}</td>
                    <td>{{data.amount}}</td>
                    <td>{{data.fee}}</td>
                    <td>{{data.date}}</td>
                    <td>

                     {{data.verify_email == 1 ? "data.username" : "data.email"}}


                    </td>


                </tr>
                       </tbody>
            </table>
        </div>

**verify_email exists in user table

Answer 1

You can use LEFT JOIN with the additional Privacy setting in the join condition, and then use COALESCE to default the receiver as anonymous:

SELECT  t.`Date`, t.amount, COALESCE(u.email, t.receiver) AS receiver
FROM `tran` t 
   LEFT JOIN `User` u 
   ON u.userName = t.receiver
   AND u.Privacy = 0;

In the case where Privacy is 1, the right hand table columns will be NULL, hence the COALESCE to default.

SqlFiddle here