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
