I have something I'm am building. To make things simple to understand I will change it all to something everybody knows.
Let's say I have a website for farmers. One table called farmers_tbl with the fields farmer_id, farmer_name, farmer_product
I have one table with a lot of grocery stores that is called stores_tbl with the following fields: store_id, store_name, store_product
The farmers can send newsletters to all the stores that are selling their product.
So, if a farmer is selling eggs, he can send the newsletter to all stores that sell eggs.
Then I can send the newsletter to all the stores that want to know about the egg offer from this farmer.PHP Code:
$sql = "SELECT * FROM stores_tbl, farmers_tbl WHERE store_product=farmer_product";
$result = mysql_query( $sql ) or die( mysql_error() );
But, a couple of stores don't want the newsletter from this farmer, so I set up a new table called unsub_tbl with the fields unsub_id, farmer_id, store_id
Now some stores can unsub from only this farmers newsletter, but continue to get the other farmers newsletter.
But, now I'm a little confused. How should I setup the query to check that I will send to all farmers that are selling eggs but not the ones that have unsubscribed to this user?
Any ideas how a query like that should look to work?