I have two table one is user and another is tran
Table User
id | userName | email |Privacy
------------+---------------+---------------+---------------
0 | user1 | email@a.com |0
25 | user2 | email1@a.com |0
150 | user3 | emai2@a.com |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 |emai2@a.com
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 <a ng-click="sort_by('tran_id');"><i class="glyphicon glyphicon-sort"></i></a></th>
<th>Type <a ng-click="sort_by('tran_type');"><i class="glyphicon glyphicon-sort"></i></a></th>
<th>Address <a ng-click="sort_by('sender');"><i class="glyphicon glyphicon-sort"></i></a></th>
<th>Amount <a ng-click="sort_by('amount');"><i class="glyphicon glyphicon-sort"></i></a></th>
<th>Feee <a ng-click="sort_by('fee');"><i class="glyphicon glyphicon-sort"></i></a></th>
<th>Date <a ng-click="sort_by('date');"><i class="glyphicon glyphicon-sort"></i></a></th>
<th>Status <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
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.
Check this. select tran.date, tran.amount, (case when user.privancy=1 then user.email else tran.receiver end) as receiver from user, tran where user.username=tran.receiver ;
select t.date, t.amount, IF(u.privacy == 1,u.email,u.username)
from User u inner join tran t on u.userName = t.receiver
Use below query
SELECT t.`Date`, t.amount, COALESCE(u.email,t.receiver) AS receiver
FROM `train` t
LEFT JOIN `test` u
ON u.userName = t.receiver
AND u.Privacy = 1
Use a case statement..... SELECT t.Date, t.amount, case when u.privacy = 1 then u.email else u.username end as receiver FROM tran t LEFT JOIN User u ON u.userName = t.receiver;