complex sql select

SeenGee

I've building an aftersales section of a site for a client. The sales themselves are dealt with using 3 tables, salesproducts, salesorders and sales_status. sales_orders is the first table used that has an autoincrement on orderid, i then use mysql_insert_id() to get this id which i then assign to salesproducts, and later sales_status. sales_status holds a var called status which holds one of 3 options: pending, confirmed or returned.
I want to be able to display to the client some basic details about pending sales, this would be the orderid, some basic product details(brand, code, quanitity) and some user information (userid).

This will actually involve 3 tables(i think!), two of the sales tables(s_orders and s_status) and the products table.

This the sql for the tables:

CREATE TABLE sales_status (
orderid int(11) NOT NULL default '0',
userid int(11) NOT NULL default '0',
status text NOT NULL
) TYPE=MyISAM;

CREATE TABLE salesorders (
orderid int(11) NOT NULL auto_increment,
order_date datetime NOT NULL default '0000-00-00 00:00:00',
userid int(11) NOT NULL default '0',
PRIMARY KEY (orderid)
) TYPE=MyISAM;

CREATE TABLE salesproducts (
ordernumber int(11) NOT NULL default '0',
productid int(11) NOT NULL default '0',
quantity int(11) NOT NULL default '0',
userid int(11) NOT NULL default '0'
) TYPE=MyISAM;

CREATE TABLE products (
productid int(11) NOT NULL auto_increment,
brand varchar(255) NOT NULL default 'Epson',
code varchar(255) NOT NULL default 'TO',
printer varchar(45) default NULL,
rrp decimal(10,2) NOT NULL default '0.00',
price decimal(10,2) NOT NULL default '0.00',
availability char(3) NOT NULL default 'in',
status text,
type varchar(255) NOT NULL default '',
productsort varchar(255) NOT NULL default 'Ink',
category varchar(255) NOT NULL default 'Ink',
view char(3) NOT NULL default 'on',
PRIMARY KEY (productid),
FULLTEXT KEY brand (brand)
) TYPE=MyISAM;

What i'm unsure of is where to start exactly, the sql needs to first check sales_status.status, where this is pending use the sales_status.orderid to check salesproducts.orderid, use sales_products.productid to find details such as products.code and products.brand.

I can see the logic but i'm not used to sql that is this complex, if anyone could give me any pointers it would be much appreciated.

Thanks.

broc7

When the order status changes, will you be changing the status in the status table or adding a new status record (for tracking over time)?

If you are only changing it, you don't need to keep the status in a separate table (although there may be other reasons to do so that I don't know about). User ID and Order ID are both already on the order table. Just add status to the order table.

Here's an untested sql attempt on your tables as presented:

SELECT products.brand, products.code, salesproducts.quantity, salesproducts.userid FROM sales_status INNER JOIN (salesproducts INNER JOIN products ON salesproducts.productid = products.productid) ON sales_status.orderid = salesproducts.ordernumber) WHERE sales_status.status = 'pending'

That's assuming salesproducts.ordernumber is the foreign key for sales_status.orderid.

Hope it works.

SeenGee

thanks for the input, the structure that has been used for the tables was chosen to aid further development although at this moment i cant get an answer as to why.

salesproducts.ordernumber is the foreign key for sales_status.orderid.

i'm trying this sql, slightly modified from your original in an attempt to make it work, i just included the other tables in the declaration at the start.

SELECT products.brand, products.code, salesproducts.quantity, salesproducts.userid
FROM sales_status , products , salesproducts
INNER JOIN (

salesproducts
INNER JOIN products ON salesproducts.productid = products.productid
) ON sales_status.orderid = salesproducts.ordernumber
)
WHERE sales_status.status = 'confirmed'
LIMIT 0 , 30

got an error on the original:
error in your SQL syntax near '(salesproducts INNER JOIN products ON salesproducts.productid = products.product' at line 1

now getting this error:
#1066 - Not unique table/alias: 'salesproducts'

Thanks a lot.

broc7

I had an extra . in products.productid. I'll take it out.

Don't list the tables up front if you're joining them later. That's why you're getting that not unique table error.

SeenGee

ok i've removed them but i'm still getting this error:

#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near ')
WHERE sales_status.status = 'confirmed'
LIMIT 0 , 30' at lin

SeenGee

seems like its missing a round bracket somewhere near the start, not too sure where though