Skip to content

Instantly share code, notes, and snippets.

@knewter
Created November 24, 2009 21:57
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 2 You must be signed in to fork a gist
  • Save knewter/242273 to your computer and use it in GitHub Desktop.
Save knewter/242273 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)
# 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