Duh why didn't I think of that.
Thank you graciously. :)
> -----Original Message-----
> From: Quentin Bennett [mailto:[EMAIL PROTECTED]]
> Sent: Thursday, February 15, 2001 4:26 PM
> To: 'Roger Ramirez'; MySQL List
> Subject: RE: Query/Left Join problem
>
>
> Hi,
>
> Your left join tried to find members in newsletter_subscriptions
> that don't
> exist in perms.
>
> How about they exist, but are 'No'?:
>
> SELECT newsletter_subscription.member_id
> FROM newsletter_subscription LEFT JOIN perms ON
> newsletter_subscription.member_id=perms.id
> WHERE newsletter_subscription.member_id<>0 AND (perms.id IS NULL OR
> mass_mail='No');
>
> Does that give you what you need?
>
> Regards
>
> Quentin
>
>
> -----Original Message-----
> From: Roger Ramirez [mailto:[EMAIL PROTECTED]]
> Sent: Friday, 16 February 2001 08:34
> To: MySQL List
> Subject: Query/Left Join problem
>
>
> Hi there.
>
> I'm having a problem with a couple of tables I'm using and finding some
> missing records and I was hoping someone could help me.
>
> Here is the setup.
>
> I have a table that is called "perms". Within here I have a bunch of
> columns, only 2 of which are important.
> ID which is an auto increment field and is my primary key for this table.
> mass_mail which has a value of 'Si' or 'No'.
> +-----------+------------------+
> | mass_mail | count(mass_mail) |
> +-----------+------------------+
> | No | 2871 |
> | Si | 8854 |
> +-----------+------------------+
>
> The second table I have is called "newsletter_subscription". This table
> also has a bunch of columns member_id which is the only important one.
> There is one record in newsletter_subscription for each record
> within perms
> where mass_mail='Si', plus a few records (almost 100) which have
> a member_id
> of 0.
>
> When I do this:
> select count(*) from newsletter_subscription where member_id<>0;
>
> I get:
> +----------+
> | count(*) |
> +----------+
> | 8857 |
> +----------+
>
> As you can see there are three more records in the newsletter_subscription
> table that should not be (8854/8857). So to find those three
> records I try
> this:
>
> SELECT newsletter_subscription.member_id
> FROM newsletter_subscription LEFT JOIN perms ON
> newsletter_subscription.member_id=perms.id
> WHERE newsletter_subscription.member_id<>0 AND perms.id IS NULL;
>
> I get:
> Empty set (0.11 sec)
>
> I need to find the three extra records that are in newsletter_subscription
> and remove them. How would I go about finding them.
>
> I also checked the newsletter_subscription table to make sure that it did
> not have any duplicate member_id's other then the few records with a
> member_id of 0. The way I did it was to do this:
> SELECT member_id, count(member_id) FROM newsletter_subscription GROUP BY
> member_id ORDER BY 2 ASC
>
> Then I look at the last couple of lines and see this:
> | 11922 | 1 |
> | 0 | 73 |
> +-----------+------------------+
> 8859 rows in set (0.10 sec)
>
> Is there a faster/easier way to do this? something like WHERE
> count(member_id) > 1 (although I know this does not work in that query)
>
>
> Thanks,
>
> Roger Ramirez
> Senior Developer
>
> TodoBebe.com - Todo lo que quiere saber de su bebé.
> TodoBebe.com - The best baby website in Spanish!
> www.todobebe.com
>
>
>
> [EMAIL PROTECTED]
>
>
> ---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
> <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> The information contained in this email is privileged and confidential
> and intended for the addressee only. If you are not the intended
> recipient, you are asked to respect that confidentiality and not
> disclose, copy or make use of its contents. If received in error
> you are asked to destroy this email and contact the sender immediately.
> Your assistance is appreciated.
>
>
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
