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
