List:General Discussion« Previous MessageNext Message »
From:SGreen Date:January 14 2005 7:26pm
Subject:Re: counting records in 2 tables using 1 query
View as plain text  
OK, then you were on the right track with your JOINS because you wanted to 
know how many of each type of item (gift or event) each user had. 

Off the top of my head, I think you need to perform an OUTER JOIN (not the 
implicit INNER JOIN you create by listing table names separated by commas) 
and use a COUNT(DISTINCT...)

http://dev.mysql.com/doc/mysql/en/JOIN.html
http://dev.mysql.com/doc/mysql/en/GROUP-BY-Functions.html

I am going to make up a table structure (adjust to fit your actual 
tables). One table, "users", has an ID and other information on it. Two 
child tables, "gifts" and "events", have an ID column, a user_ID column, 
and other information.

SELECT u.ID, COUNT(DISTINCT g.ID) as gifts, COUNT(DISTINCT e.ID) as events
FROM users u
LEFT JOIN gifts g
        ON g.user_ID = u.ID
LEFT JOIN events e
        ON e.user_ID = u.ID
GROUP BY u.ID;

That should tell you how many of each item (gift or event) belongs to each 
user. Both COUNT() and COUNT(DISTINCT ) ignore NULLS but the DISTINCT 
modifier helps by eliminating duplicates. If you don't think this query 
originally contained duplicates try this non-GROUPED version of the query 
and look at the data.

SELECT u.ID, g.ID,  e.ID
FROM users u
LEFT JOIN gifts g
        ON g.user_ID = u.ID
LEFT JOIN events e
        ON e.user_ID = u.ID
ORDER BY u.ID, g.id, e.id
LIMIT 200;

What you should see is that every possible combination of GIFT and EVENT 
for each user is present in this data** . The DISTINCT modifier eliminated 
those duplicates from our totals.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

NOTE:  **By joining any two tables you form a "Cartesian product" of the 
data on those tables. You restrict the results of a Cartesian product by 
putting conditions in the ON clause of an explicit JOIN or in the WHERE 
clause of an implicit JOIN. We were able to restrict the rows returned 
from the  USERS-GIFTS join and the USERS-EVENTS join. However, we had no 
way to isolate the GIFTS-EVENTS combinations to reduce gift duplication so 
their full product became part of the source data for our SELECT...GROUP 
BY operations. 

If, for instance you had a field on the GIFTS table that determined what 
EVENT the gift was for, then we could make that restriction by adding it 
as a term to either one of our ON clauses or as a WHERE restriction. 
Something like:

SELECT u.ID, g.ID,  e.ID
FROM users u
LEFT JOIN events e
        ON e.user_ID = u.ID
LEFT JOIN gifts g
        ON g.user_ID = u.ID
        AND g.event_ID = e.ID
ORDER BY u.ID, g.id, e.id

see the difference? 

-S


2wsxdr5 <2wsxdr5@stripped> wrote on 01/14/2005 01:18:18 PM:

> SGreen@stripped wrote:
> 
> >
> > There may be other ways to get at the information you want. What is 
> > the purpose of your query?
> >
> Ok here are the details.  I have a wish list/gift registry site 
> (thewishzone.com).  I have a table listing all the data on my users.  I 
> also have a table listing all the gifts my users want.  Finally I have a 

> table with gift giving events for the users.  What I need to know is how 

> many events and how many gifts each user has in the database so I can 
> make certain changes to the content of the main user page on my site. 
> Right now I just use 2 queries but I would like to do it in one just to 
> reduce the code some what. I have other uses for similar queries but 
> this is the main reason.
> 
> Chris W
> 
> 
> > 2wsxdr5 <2wsxdr5@stripped> wrote on 01/13/2005 01:57:31 PM:
> >
> > > I have these 2 queries.
> > >
> > > SELECT count(*) gifts
> > > FROM   gift g
> > > WHERE g.this and g.that
> > >
> > > SELECT count(*) events
> > > FROM   events e
> > > WHERE e.this and e.the other thing
> > >
> > > is there a way to put these into one query.....
> > >
> > > SELECT count(g.*) gifts, count(e.*)
> > > FROM gift g, event e
> > > WHERE . . . .
> > >
> > > so far nothing seems to be working
> > >
> > > --
> > > Chris W
> > >
> > > Gift Giving Made Easy
> > > Get the gifts you want & give the
> > > gifts they want this holiday season
> > > http://thewishzone.com
> > >
> > > "They that can give up essential liberty
> > > to obtain a little temporary safety
> > > deserve neither liberty nor safety."
> > > -- Benjamin Franklin, 1759 Historical Review of Pennsylvania
> > >
> > > --
> > > MySQL General Mailing List
> > > For list archives: http://lists.mysql.com/mysql
> > > To unsubscribe: http://lists.mysql.com/mysql?unsub=1
> > >
> 
> 
> 
> -- 
> Chris W
> 
> Gift Giving Made Easy
> Get the gifts you want & 
> give the gifts they want
> http://thewishzone.com
> 

Thread
counting records in 2 tables using 1 query2wsxdr513 Jan
  • Re: counting records in 2 tables using 1 queryPeter Brawley13 Jan
  • Re: counting records in 2 tables using 1 querySGreen13 Jan
    • Re: counting records in 2 tables using 1 query2wsxdr514 Jan
      • Re: counting records in 2 tables using 1 querySGreen14 Jan