Page 1 of 1

How to use join 3 tables

Posted: Mon Nov 05, 2007 6:03 am
by shivam0101
I want to send email to all members whenever a product is added. I also need to track the ids of the member_ids to whom mail's are successfully sent, so that if not sent again i should be able to send
for this sent table is used. Once all members gets mail, prod_flag will be set to 1



table structure
product
product_id
product_name
prod_flag

member
member_id
member_name

sent
product_id
member_id

I am trying to get all those member_ids and products whose flag=0 and not in sent table.

Code: Select all

"SELECT * FROM members, product WHERE  AND member_id NOT IN sent"
// struct here

how to get product ids which are not in sent table.

Posted: Mon Nov 05, 2007 9:44 am
by feyd
There is a thread specifically about requesting data that is not in another table which can be found in Useful Posts.

Posted: Tue Nov 06, 2007 7:52 pm
by shivam0101
i am not getting.

I tried using,

SELECT members.member_id
FROM members
LEFT JOIN sent
ON members.member_id = sent.member_id

which shows all the ids. When i change to RIGHT, it shows all the member_id from sent table. How to join the prod_id in the above query?

I want all members whom the mail has not been sent for that particular product.

Posted: Sun Nov 11, 2007 8:40 pm
by iceangel89
where product.product_id not in (select sent.product_id from sent)?