Join not working

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
shivam0101
Forum Contributor
Posts: 197
Joined: Sat Jun 09, 2007 12:09 am

Join not working

Post 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
Hemlata
Forum Commoner
Posts: 35
Joined: Mon Sep 10, 2007 5:40 am
Location: India
Contact:

Post by Hemlata »

Hello,

What error you are getting for the same..


Regards,
shivam0101
Forum Contributor
Posts: 197
Joined: Sat Jun 09, 2007 12:09 am

Post by shivam0101 »

does not return anything when the newsletter_sent table is empty.
Hemlata
Forum Commoner
Posts: 35
Joined: Mon Sep 10, 2007 5:40 am
Location: India
Contact:

Post 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,
shivam0101
Forum Contributor
Posts: 197
Joined: Sat Jun 09, 2007 12:09 am

Post 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.
Hemlata
Forum Commoner
Posts: 35
Joined: Mon Sep 10, 2007 5:40 am
Location: India
Contact:

Post 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,
shivam0101
Forum Contributor
Posts: 197
Joined: Sat Jun 09, 2007 12:09 am

Post 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?
Hemlata
Forum Commoner
Posts: 35
Joined: Mon Sep 10, 2007 5:40 am
Location: India
Contact:

Post 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
shivam0101
Forum Contributor
Posts: 197
Joined: Sat Jun 09, 2007 12:09 am

Post 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
Hemlata
Forum Commoner
Posts: 35
Joined: Mon Sep 10, 2007 5:40 am
Location: India
Contact:

Post 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,
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Look for a thread on queries of exclusion in Useful Posts. A link is currently provided in my signature.
Post Reply