Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save ddollar/242282 to your computer and use it in GitHub Desktop.
Save ddollar/242282 to your computer and use it in GitHub Desktop.
# Person has_many cards has_many transaction_feeds.
# I would like to find all of the people with at least one transaction feed with the is_ach bool set to true:
SELECT DISTINCT(people.id) AS person_id FROM `people`
INNER JOIN `cards` ON cards.person_id = people.id
INNER JOIN `transaction_feeds` ON transaction_feeds.card_id = cards.id
WHERE (transaction_feeds.is_ach = 1)
GROUP BY person_id
HAVING COUNT(transaction_feeds.id) > 0;
# This works.
# I would like to find all of the people with no transaction feeds that have that is_ach bool set to true.
# I don't know how to do this.
# NAIVE ATTEMPT ONE:
SELECT DISTINCT(people.id) AS people_id FROM `people`
INNER JOIN `cards` ON cards.person_id = people.id
LEFT JOIN `transaction_feeds` ON transaction_feeds.card_id = cards.id
WHERE (transaction_feeds.is_ach = 1)
GROUP BY person_id
HAVING COUNT(transaction_feeds.id) = 0;
Empty set (0.42 sec)
USE THIS:
SELECT DISTINCT(people.id) AS people_id FROM `people`
INNER JOIN `cards` ON cards.person_id = people.id
LEFT JOIN `transaction_feeds` ON transaction_feeds.card_id = cards.id AND transaction_feeds.is_ach = 1
GROUP BY person_id
HAVING COUNT(transaction_feeds.id) = 0;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment