How to use join 3 tables

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

How to use join 3 tables

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

Post by feyd »

There is a thread specifically about requesting data that is not in another table which can be found in Useful Posts.
shivam0101
Forum Contributor
Posts: 197
Joined: Sat Jun 09, 2007 12:09 am

Post 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.
iceangel89
Forum Commoner
Posts: 39
Joined: Mon Jul 02, 2007 7:02 pm

Post by iceangel89 »

where product.product_id not in (select sent.product_id from sent)?
Post Reply