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_p
Yes, I don't see why that wouldn't work...
On Mon, Nov 2, 2009 at 11:09 AM, Brian Dunning wrote:
> Johnny - Your solution might actually help me solve my next step, which is
> to also return a count of pets bought only within the last 7 days. Something
> like this:
>
>
>
>> SELECT people.*,
>>
Johnny - Your solution might actually help me solve my next step,
which is to also return a count of pets bought only within the last 7
days. Something like this:
SELECT people.*,
SUM(IF(cars.id IS NULL,0,1)) AS car_count,
SUM(IF(pets.id IS NULL,0,1)) AS pet_count,
SUM ( IF ( pets.d
Thanks, this solved it!
On Nov 2, 2009, at 12:37 AM, Michael Dykman wrote:
I suspect 'distinct' might help you out here.
SELECT
people.*,
count(distinct cars.car_id) as car_count,
count(distinct pets.pet_id) as pet_count
--
MySQL General Mailing List
For list archives: htt
You are asking for all records form all tables. So, If 1 person has 1 car
and 1 pet, there will be 2 records returned for that 1 person.
You'll need to use SUM() instead of COUNT():
SELECT people.*,
SUM(IF(cars.id IS NULL,0,1)) AS car_count,
SUM(IF(pets.id IS NULL,0,1)) AS pet_count,
Ma
Hi all -
I have a table of PEOPLE, and a table of CARS owned by various people,
and a table of PETS owned by various people. Each person may have 0 or
more pets, and each person may have 0 or more cars. I'm trying to
return a list of all the people, showing how many pets each person
has,
> -Ursprüngliche Nachricht-
> Von: Renito 73 [mailto:[EMAIL PROTECTED]
> Gesendet: Samstag, 30. September 2006 04:20
> An: mysql@lists.mysql.com
> Betreff: Joining *3* tables
>
> Hello list
Hello Mr 73,
> I have a large database of contacts, but since not
Hello list
I have a large database of contacts, but since not all fields are used I
decided to separate all information in 3 tables to save space like this:
DB_ADDRESS
id int
address char(128)
... three columns more ...
DB_COMPANY
id int
company char(64)
DB_LISTS
id int
list char(16)
Not all
Amy,
You didn't mention what the problem is with your query...
select reference.uid
from reference, subject_name, ref_cat
where subject_name.sub_id = '45'
and ref_cat.ref_cat_id = '3'
and ref_cat.ref_cat_id = reference.ref_cat_id
and subject_name.sub_id = ref_cat.sub_id
IAC it is easier to unde
On Tue, 14 Feb 2006, Amy Thornton wrote:
> I am trying to join 3 tables together.
>
> Table A has 3 fields: sub_id, subject, id
> Table B has 3 fields: ref_cat_id, sub_id, ref_cat
> Table C has 7 fields: uid, ref_cat_id, etc.
>
> I am trying to join Table A and B over sub_id and join B and C over
I am trying to join 3 tables together.
Table A has 3 fields: sub_id, subject, id
Table B has 3 fields: ref_cat_id, sub_id, ref_cat
Table C has 7 fields: uid, ref_cat_id, etc.
I am trying to join Table A and B over sub_id and join B and C over ref_cat_id
while pulling in the value of sub_id and r
11 matches
Mail list logo
