You'll need a left join to do that query. Something like:
select member.id, member.email, count(xref.email_id) as not_read
from member left join xref on member.id = xref.member_id where not_read == 0
and xref.email_id = 283 group by member.id;
where xref is the email/member cross reference table and 283
is the particular email you're running the query about.
Rene
At 03:54 PM 8/19/01, Daren Cotter wrote:
>The data wouldn't need to be stored for any longer than two to three months,
>so that shouldn't be a problem...what about my query to get all members that
>have not read the mailing, is that possible (assuming I don't use the
>reverted logic you were talking about).
>
>
>
>Daren Cotter
>CEO, InboxDollars.com
>http://www.inboxdollars.com
>(507) 382-0435
>
>-----Original Message-----
>From: Rene Churchill [mailto:[EMAIL PROTECTED]]
>Sent: Sunday, August 19, 2001 7:34 AM
>To: Daren Cotter
>Cc: [EMAIL PROTECTED]
>Subject: Re: normalization question
>
>
>
>Hi Daren,
>
>How long do you need to store this information? The normalized table
>that you describe is simple and easy to index. Having several million
>rows in the table won't bother MySQL. So is it sufficient to keep the
>data around for a month and then prune it out of the database? Long
>term records could be dumped out into text logfiles for safe keeping.
>
>Another possiblity is to invert your storage logic. By that, I mean
>insert a row in the cross-reference table for every user that has
>NOT read the email. Then when they read it, remove that row from
>the table. This will automatically prune down the table size as
>more and more of your readers check their email.
>
>I would HIGHLY suggest using the DELAYED option on that size of an
>insert.
>
>You may not want to go this logic inversion route because of the havoc
>it will play with other queries. For example, since any user that
>does NOT have a matching row in the cross-reference table is assumed
>to have read the message, any new user joining you will automatically
>be assumed to have read all messages to date.
>
> Rene
>
>Daren Cotter wrote:
>> I have a table which needs to store which emails each member has read and
>> had their account credited for. Currently, I have a field "read_array
>> longtext" in my main members table, and each time a member reads an email,
>> it simply concats to the end of this array. For example, they read email
>> 288, their read array is:
>>
>> 288
>>
>> They read 298, the read_array is:
>>
>> 288||298
>>
>> Using this method (which is far from normalization, I know), I have been
>> able to accomplish the two major things I need to be able to accomplish:
>>
>> 1) Insure that no member can get credit for reading the same mailing twice
>> (check their current read_array, using ereg())
>> 2) Run a query to see how many members have read each mailing (WHERE
>> read_array LIKE '%mail_id%')
>>
>> I know that both of these tasks would be easier if I created a new table,
>> and stored the member_id, mail_id, and date, and the table would then be
>> normalized...however, I send approximately one new mailing per day, and
>this
>> would mean 100,000 new rows in this table every day. Can somone offer me
>> "expert" advice as to which method is better? Using an array that will
>grow
>> to unlimited size, or using a table that is going to grow much larger
>every
>> day?
>>
>> Also, as a deterrant to switching to the normalized method...I frequently
>> run queries to do the following:
>>
>> *) After I send a mailing, a week later, I send the mailing again to all
>> members who did not confirm reading the email. My Query is like:
>> select member_id, (more) FROM members WHERE read_array not like
>'%mail_id%'
>>
>> Is there any possible way I could accompish this task in one query if I
>had
>> this process normalized?
>
>--
>Rene Churchill http://www.vtwebwizard.com
>Internet Consulting 802-244-5151
>Specializing in Web Programming
>
>---------------------------------------------------------------------
>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
--
René Churchill [EMAIL PROTECTED]
Vermont Web Wizard, LLC 802-244-5151
Specializing in Web Programming 802-244-5512 (fax)
http://www.vtwebwizard.com
---------------------------------------------------------------------
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
