since this SQL schemas I want to count the number of times a user is in a contest.
SELECT * FROM users u
LEFT JOIN trials_has_users tu ON (tu.users_id = '1')
LEFT JOIN trials AS t ON (t.id = tu.trials_id)
WHERE u.id = '1';
Previously, I have the expected number of lines, but I want to make a count
SELECT contest_total FROM users u
LEFT JOIN trials_has_users tu ON (tu.users_id = '1')
LEFT JOIN (
SELECT
id,
COUNT(*) AS contest_total
FROM
trials
WHERE deleted_at IS NULL
GROUP BY
id
) AS t ON (t.id = tu.trials_id)
WHERE u.id = '1';
Previously, have 6 rows want 1 (current user id) need LIMIT 1
?
SELECT contest_total FROM users u
LEFT JOIN trials_has_users tu ON (tu.users_id = '1')
LEFT JOIN (
SELECT
id,
COUNT(*) AS contest_total
FROM
trials
WHERE deleted_at IS NULL
GROUP BY
id
) AS t ON (t.id = tu.trials_id)
WHERE u.id = '1'
LIMIT 1;
I would like to receive the number of contests in which the user has participated. My query is right ?