Created
November 24, 2009 21:57
-
-
Save knewter/242273 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) | |
# To clarify, and for _raymond_... | |
# I am attempting to write a query that will find all people that do not have any associated | |
# transaction_feeds with the is_ach bool set to true. That is, either people with NO | |
# transaction_feeds, or people with only non-ach transaction_feeds. If a person has a single | |
# transaction_feed with that bool set, they should not show up in the set for the query I'm | |
# attempting to write. That is all. |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment