I said something wrong: Count(Distinct ) counts how many unique values appear in a set. NOT how many times each unique value appears in a set.
Shawn [EMAIL PROTECTED] wrote on 08/03/2004 10:28:33 AM: > Ok, I haven't completely tested this but I have confidence in the > approach..... > > If you have the tables: campaign, foo, bar, example, and another > And, each table has something unique per row (like an ID column) > Then, this query should give you the number of rows returned from each > table because COUNT(DISTINCT ) ignores null values. > > SELECT count(distinct campaign.id)as Campaign_name > , count(distinct foo.id) as foo > , count(distinct bar.id) as bar > , count(distinct example.id) as example > , count(distinct another.id) as another > FROM campaign > XX JOIN foo > on foo join conditions > XX JOIN bar > ON bar join conditions > XX JOIN example > on example join conditions > XX JOIN another > on another join conditions > WHERE overall where conditions > > XX can be either RIGHT, LEFT, or INNER and the join conditions are > whatever you need them to be to tie the tables together into a > relationship. The count(distinct ) count how many times each unique value > appears in any column. Since the values we chose to count are also unique > in the table (one row per value), this query also tells you how many > records in your joined tables matched your ON conditions. > > Shawn Green > Database Administrator > Unimin Corporation - Spruce Pine > > > Scott Haneda <[EMAIL PROTECTED]> wrote on 08/02/2004 09:14:59 PM: > > > Here is what I am doing now, in mysql 4.x > > > > This gives me a name/id list from a table > > SELECT campaign_name, id > > FROM campaigns > > WHERE user_id = 123; > > > > > > This will tell me which of the above name/id's are in use in another > table. > > SELECT distinct(campaign_id) > > FROM addresses_incampaign > > WHERE user_id = 123 > > ORDER BY campaign_id"; > > > > The reason I need to know which campaign_id's are used in the second > table, > > is to see if they are allowed to delete the record from campaigns, if > there > > are matching records in both tables, they are not allowed to delete. > > > > Somehow, I am looking to get a single SQL query to give me a result with > a > > count as a int > > Campaign_name records_in addresses_incampaign > > foo 15 > > bar 12 > > example 0 (safe to delete) > > anohther 2 > > > > -- > > ------------------------------------------------------------- > > Scott Haneda Tel: 415.898.2602 > > http://www.newgeo.com Fax: 313.557.5052 > > [EMAIL PROTECTED] Novato, CA U.S.A. > > > > > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > >
