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.
$sql = "SELECT * FROM stores_tbl, farmers_tbl WHERE store_product=farmer_product";
$result = mysql_query( $sql ) or die( mysql_error() );
Then I can send the newsletter to all the stores that want to know about the egg offer from this farmer.
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?
According to ur query u r selecting all the records from both the table.
if u have to send newsletters to all store on behalf of farmers then select only those stores those sales the eggs. then for each farmer who sell eggs notify the the stores to those stores who sell eggs.
CUPS. Well, this was just an example. My tables are far more complicated than this.
I just wanted to know how to check both tables for the data.
I want to know how to write a query that checks if one farmer is selling eggs and also that he didn’t unsubscribe to this users newsletter.
Since he want a newsletter from all the other egg farms. That’s why I built a separate table to hold the unsubscriptions in. But, how do I check both places?
So you want to select only those shops that are not present in the unsub table for that farmer?
Do a LEFT OUTER JOIN of the unsub table
SELECT *
FROM stores_tbl s
INNER JOIN farmers_tbl f
ON s.store_product = f.farmer_product
LEFT OUTER JOIN unsub_tbl u
ON u.farmer_id = f.farmer_id
AND u.store_id = s.store_id
WHERE u.farmer_id IS NULL
The where condition will have the query return only those farmer/shop combinations that aren’t present in the unsub table
StarLion, you are right, but sometimes you don’t want to give away an idea. This was a project I have big plans for. Not sure I want anyone to pick up the idea.
I have a lot of great web ideas, so I try to work with a lot of them and then put together bits and pieces and see if it works the way I want it.
If I would show the real tables, I guess someone could get the idea and figure out that it was something great…