Page 1 of 1

Join not working

Posted: Wed Sep 26, 2007 12:52 am
by shivam0101

Code: Select all

SELECT * FROM members, newsletter_sent WHERE members.member_flag=1 AND newsletter_sent.newsletter_id !=12 AND newsletter_sent.member_id !=members.member_id

The query is not working :(


members
member_id


newsletter_sent
newsletter_sent_id- PK
newsletter_id
member_id

Posted: Wed Sep 26, 2007 1:51 am
by Hemlata
Hello,

What error you are getting for the same..


Regards,

Posted: Wed Sep 26, 2007 4:37 am
by shivam0101
does not return anything when the newsletter_sent table is empty.

Posted: Wed Sep 26, 2007 4:44 am
by Hemlata
hello,

Naturally the query will return empty record set if any of the table is empty. What exactly you are looking for?

Regards,

Posted: Wed Sep 26, 2007 5:45 am
by shivam0101
i want member_id's who did not did not receive that particular newsletter.

i am checking this before sending newsletter to member, once it has been sent, i am inserting member_id and newsletter_id into newsletter_sent table.

Posted: Wed Sep 26, 2007 6:00 am
by Hemlata
Hello,
i want member_id's who did not did not receive that particular newsletter.

i am checking this before sending newsletter to member, once it has been sent, i am inserting member_id and newsletter_id into newsletter_sent table.
and your query
SELECT * FROM members, newsletter_sent WHERE members.member_flag=1 AND newsletter_sent.newsletter_id !=12 AND newsletter_sent.member_id !=members.member_id
will defiantly wont give you the required results as in query you are joining the records for the 'newsletter_sent' and 'members' table...

If you want to check whether the member is sent with the current newsletter, then simply check the current 'newsletter_id' and 'member_id' in table 'newsletter_sent', and if no record is found then insert the record else do other stuff..

Regards,

Posted: Wed Sep 26, 2007 6:27 am
by shivam0101
Thanks.

But, i won't be knowing member_id's. I'll prepare newsletter then submit, it will be entered into database, i'll get that newsletter_id from mysql_insert_id. For sending newsletters to members, i have to check newsletter_sent table with members table. Should i have to write 2 queries?

Posted: Wed Sep 26, 2007 6:50 am
by Hemlata
Hello,
But, i won't be knowing member_id's. I'll prepare newsletter then submit, it will be entered into database, i'll get that newsletter_id from mysql_insert_id. For sending newsletters to members, i have to check newsletter_sent table with members table. Should i have to write 2 queries?
If you are inserting new newsletter, then definitely none of the members will be sent with the same newsletter, so why dont you directly sent the newsletter directly to all the members that are subscribed for newsletter.

Regards

Posted: Wed Sep 26, 2007 6:57 am
by shivam0101
Yes, I can do that. Again, If there are many members and the process stops for some reason, i wont be knowing the members yet to receive newsletters

Posted: Wed Sep 26, 2007 7:14 am
by Hemlata
Hello,
Yes, I can do that. Again, If there are many members and the process stops for some reason, i wont be knowing the members yet to receive newsletters
Then the solution should be to first collect all the members that are sent with the current newsletter_id and get the missing members using the second query. So, collect all the members id here having current newsletter

Code: Select all

"SELECT * FROM `newsletter_sent` WHERE  newsletter_sent.newsletter_id ='$current)newsletter_id'";
Then do a select on 'members table and get all those id that is not in the newsletter_sent'_array

Code: Select all

$members = implode(', ', $newsletter_sent_member_id);
"SELECT * FROM `members` WHERE  member_id  NOT IN ($members);
From here you can get the members that are still pending with the current newsletter..

Hope this might solve your issue.

Regards,

Posted: Wed Sep 26, 2007 9:32 am
by feyd
Look for a thread on queries of exclusion in Useful Posts. A link is currently provided in my signature.