-
-
Save ddollar/242282 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# 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