How can I write a query that answers the following question: Which stores
have not entered soups in the collection_schedule for the current day? I
just want to get a list that shows the stores.code, stores.name,
stores.phone of all the stores that failed to update the soups for the day.
collection_schedule.store_id is the same as stores.id and I'm using:
$day_stamp = date("Y").date("m").date("d"); // for collection_schedule.day.
I have tried using a left join but I don't understand how it works well
enough to get the results I need. Can anyone help me out with this?
Thanks for any assistance!!!
These are the two tables:
##########################################################
# Table structure for table 'collection_schedule'
#
CREATE TABLE collection_schedule (
id int(10) unsigned NOT NULL auto_increment,
soups varchar(255) NOT NULL,
store_id int(10) unsigned DEFAULT '0' NOT NULL,
day timestamp(8),
PRIMARY KEY (id),
UNIQUE day_dupe (store_id, day)
);
// used for storing a list of soups that are updated daily
##########################################################
# Table structure for table 'stores'
#
CREATE TABLE stores (
id int(10) unsigned NOT NULL auto_increment,
code varchar(6) NOT NULL,
username varchar(12) NOT NULL,
password varchar(16) NOT NULL,
name varchar(40) NOT NULL,
store_owner varchar(65) NOT NULL,
address1 varchar(30) NOT NULL,
address2 varchar(30) NOT NULL,
city varchar(30) NOT NULL,
state char(2) NOT NULL,
zip varchar(12) NOT NULL,
phone varchar(12) NOT NULL,
fax varchar(12) NOT NULL,
email varchar(40) NOT NULL,
tax_rate float(10,8) unsigned DEFAULT '0.00000000' NOT NULL,
welcome_back text NOT NULL,
welcome_new text NOT NULL,
confirm_email text NOT NULL,
order_email text NOT NULL,
menu_order varchar(255) NOT NULL,
status enum('A','I') DEFAULT 'A' NOT NULL,
test enum('Y','N') DEFAULT 'N' NOT NULL,
open enum('Y','N') DEFAULT 'N' NOT NULL,
fax_opt enum('Y','N') DEFAULT 'Y' NOT NULL,
directions text NOT NULL,
PRIMARY KEY (id),
UNIQUE code (code)
);
// stores various information about stores
