CODEWITHSUNDEEP
mysql
Igor Ciqueira
Igor Ciqueira

Reputation: 7

Mysql join with limits

My query is like this....

SELECT tb1.name, COUNT(tb2.payment_date) as first_payment FROM table1 LEFT JOIN table2 tb2 ON tb2.id_user = tb1.id

+-----------+-------------+
| Name      |  Count      |
+-----------+-------------+
| John      |   543534543 |

but I wish that my query returns a limit on join, something like:

LEFT JOIN tb2 ON tb2.id_user = tb1.id { LIMIT JOIN }

return only one relationship.....

+-----------+-------------+
| Name      |  Count      |
+-----------+-------------+
| John      |   3         |

Upvotes: 0

Views: 52

Answers (2)

Muhammad Raheel
Muhammad Raheel

Reputation: 19882

SELECT 
    tb1.name, 
    COUNT(tb2.payment_date) as first_payment 
FROM table1 
LEFT JOIN (SELECT id_user , MIN(id) FROM table2 GROUP BY id_user)as tb2 ON tb2.id_user = tb1.id

Upvotes: 0

Raphaël Althaus
Raphaël Althaus

Reputation: 60503

select tb1.name, count(*) as first_payment
from table1 t1
LEFT JOIN (SELECT id_user, min(payment_date) 
           FROM table2
           GROUP BY id_user) as t2
ON t1.id = t2.id_user
GROUP BY tb1.name

Upvotes: 1

Related Questions