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 <[EMAIL PROTECTED]> wrote on 01/14/2005 01:18:18 PM: > [EMAIL PROTECTED] 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 <[EMAIL PROTECTED]> 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/[EMAIL PROTECTED] > > > > > > > -- > Chris W > > Gift Giving Made Easy > Get the gifts you want & > give the gifts they want > http://thewishzone.com >
