tux-world tux-world - 3 months ago 26
MySQL Question

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

Answer

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
   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