I need some assistance with some SQL

[Guest]

Hi all,

 

I am having some difficulty putting together an SQL query which selects a set of "product_id" based on criteria from several other osCommerce tables:

 

|----------------|	 |--------------------|
| products	   |	 | orders_products	|
|----------------|	 |--------------------|
| products_id	|	 | orders_products_id |
| products_model |	 | orders_id		  |
|----------------|	 | products_id		|
				   | products_model	 |
				   |--------------------|


|---------------|	  |---------------------|
| orders		|	  | customers_basket	|
|---------------|	  |---------------------|
| orders_id	 |	  | customers_basket_id |
| orders_status |	  | products_id		 |
|---------------|	  |---------------------|

 

I need to select a list of "product_id" which correspond to dynamically generated products which are no longer required; I need to select the list of "product_id" to be removed based on the following criteria:

 

1. Only products with a "products_model" equal to the text value 'CUSTOM' should be selected.

2. None of the "product_id" selected can have a corresponding entry in the "orders_products" table where the associated "orders_status" field in the "orders" table is not equal to the integer value '3'.

3. None of the "product_id" selected can have any associated entry in the "customer_basket" table

 

If anyone can help me get this sorted I really would be most grateful, I have been struggling with this for hours (SQL isn't one of my stronger skills).

 

 

Cheers,

Paul

[Guest]

44 reads but no reply; am I to take it that what I need to do isn't possible within one query, or have the resident SQL guru's just not swooped on this one yet?

[Guest]

SELECT p.products_id 
 FROM products p
 JOIN orders_products op
ON op.products_id = p.products_id
 JOIN orders o
ON o.orders_id = op.orders_id
  AND o.orders_status = 3
 LEFT
 JOIN customers_basket cb
ON cb.products_id = p.products_id
WHERE p.products_model = 'CUSTOM' 
  AND cb.customers_id IS NULL;

 

This query pretty much does what I require; I will run a seperate query to identify those products which do not appear in either the "customers_basket" table or the "orders" table.

 

This code was provided by a kind soul called Cafelatte over at the Devshed MySQL forums.

 

Cheers,

Paul