List:General Discussion« Previous MessageNext Message »
From:Brian Dunning Date:November 3 2009 4:47pm
Subject:Re: Trouble joining 3 tables
View as plain text  
Darn, it's not working after all.

SELECT
people.*,
COUNT ( DISTINCT cars.car_id ) AS car_count,
COUNT ( DISTINCT pets.pet_id ) AS pet_count,
SUM ( IF ( pets.date_bought > NOW() - INTERVAL 7 DAY, 1, 0 ) ) AS  
new_pet_count
WHERE...etc

car_count and pet_count are calculating correctly, but new_pet_count  
is only correct if the person has 0 new pets. If they have more than  
zero, new_pet_count evaluates to pet_count * 2. It seems like I need a  
way to combine a "distinct pets.pet_id" with the sum.

Thread
Trouble joining 3 tablesBrian Dunning2 Nov
  • Re: Trouble joining 3 tablesJohnny Withers2 Nov
    • Re: Trouble joining 3 tablesBrian Dunning2 Nov
      • Re: Trouble joining 3 tablesJohnny Withers2 Nov
        • Re: Trouble joining 3 tablesBrian Dunning3 Nov
Re: Trouble joining 3 tablesBrian Dunning2 Nov