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]
>
