CODEWITHSUNDEEP
mysql
tux-world
tux-world

Reputation: 2710

MySql return empty result when one of table for JOIN is not any data

with below Sql query i can get result successful when all of tables has data, but in this my query when transactions table has not any saved data and its empty my query return empty result, but i want to get null or empty columns data

SELECT transactions.id, 
       userEwallets.ewalletNumber, 
       userEwallets.currencySymbol, 
       transactions.money, 
       transactions.transactionType, 
       b.username AS toUser, 
       a.username AS sender 
FROM transactions
   JOIN userEwallets ON transactions.ewalletId = userEwallets.id
   LEFT JOIN users AS b ON b.id = transactions.toUserId
   LEFT JOIN users AS a ON a.id = transactions.fromUserId
WHERE transactions.userId = 37

when its not empty i get this result:

id  ewalletNumber       currencySymbol  money   transactionType toUser  sender
95  SHIRR9373036569     IRR 20          1       1                   amin

Upvotes: 2

Views: 1894

Answers (3)

Paul Spiegel
Paul Spiegel

Reputation: 31772

you can use a dummy table with one row. The other tables should be left joined to it.

SELECT transactions.id, 
       userEwallets.ewalletNumber, 
       userEwallets.currencySymbol, 
       transactions.money, 
       transactions.transactionType, 
       b.username AS toUser, 
       a.username AS sender 
FROM (select 1) dummy
   LEFT JOIN transactions ON transactions.userId = 37
   LEFT JOIN userEwallets ON transactions.ewalletId = userEwallets.id
   LEFT JOIN users AS b ON b.id = transactions.toUserId
   LEFT JOIN users AS a ON a.id = transactions.fromUserId

You can allways use a subquery instead of a table name if you give it an allias. Note that you have to move the WHERE condition for the left joined table into the ON clause - Othewise MySQL will convert it to an INNER JOIN.

Upvotes: 2

Redithion
Redithion

Reputation: 1006

In order to accomplish that, you should use another table to be the first one. The way you are doing it, if there are no records in the transactions table then there would be nothing to join with.
So, use a table that always has records and LEFT JOIN it with the others.
Try this:

SELECT transactions.id, 
   userEwallets.ewalletNumber, 
   userEwallets.currencySymbol, 
   transactions.money, 
   transactions.transactionType, 
   b.username AS toUser, 
   a.username AS sender 
FROM users AS b
LEFT JOIN transactions ON b.id = transactions.toUserId
LEFT JOIN users AS a ON a.id = transactions.fromUserId
JOIN userEwallets ON transactions.ewalletId = userEwallets.id
WHERE transactions.userId = 37

Upvotes: 0

Sailesh Babu Doppalapudi
Sailesh Babu Doppalapudi

Reputation: 1544

You are using where condition here. where transactions.userId = 37. If there is no data in transactions table, that where condition will fail and hence returns no data

Upvotes: 0

Related Questions