JOIN HELP

[zink]

Hi there

 

I am super confused about JOINS! Basically I am writing a report and all I need it to do is extract the number of sales each product had within specified date boundries.

 

I can get the products out of the database for a particular manufacturer (you select manufacturer in teh admin panel) and display it in a table so looks like this (with some data)

 

---------------------------------------------------------------------------

Manufacturer | Track Name | Artist | Cat Number | Price | All time Sales | Extra Column here for sales durinf period!

---------------------------------------------------------------------------

 

Here are my two queries:

 

---------------------------------------------------------------------------------------------

Query that generates proudct info

this works fine and brings back all of the products and info for that manufacturer

---------------------------------------------------------------------------------------------

 

select p.products_name, op.products_model, op.products_price, op.products_ordered, pp.products_extra_fields_value, pp.products_extra_fields_id
FROM products AS op
LEFT JOIN products_description AS p ON p.products_id = op.products_id
LEFT JOIN products_to_products_extra_fields AS pp ON pp.products_id = op.products_id
LEFT JOIN manufacturers AS m on op.manufacturers_id = m.manufacturers_id
WHERE m.manufacturers_id = '10' AND pp.products_extra_fields_id = '8'
ORDER BY m.manufacturers_name

 

-------------------------------------------

Query that pulls out orders via date

-------------------------------------------

 

select op.products_quantity, op.final_price
FROM " . TABLE_ORDERS_PRODUCTS . " AS op
LEFT JOIN " . TABLE_PRODUCTS . " AS p ON op.products_id = p.products_id
LEFT JOIN " . TABLE_MANUFACTURERS . " AS m on p.manufacturers_id = m.manufacturers_id
LEFT JOIN " . TABLE_ORDERS . " AS o ON op.orders_id = o.orders_id
WHERE o.date_purchased BETWEEN '" . $start_date . " 00:00:00' AND '" . $end_date . " 23:59:59'
AND m.manufacturers_id = '10'
ORDER BY m.manufacturers_name

 

 

What I can't figure is how to integrate the two queries so it pulls out the number of sales between my two date boundries.

 

Please help, I have been trying to do this for days now and thought it was time to ask for help! :(

 

If you need I will send you my full page code

 

Hope this makes sense!

 

Cheers

[chipshot]

you could install a contribution like : http://addons.oscommerce.com/info/5712

[zink]

you could install a contribution like : http://addons.oscommerce.com/info/5712

 

Thanks but not really what i'm after... I just need someone with good oscommerce knowledge to help me on this query above.

[chipshot]

knowledge of OSC really has nothing to do with it it's a sql query that you are looking for check this out: also this

[zink]

knowledge of OSC really has nothing to do with it it's a sql query that you are looking for check this out: also this

 

I get the theory of joins but just can't visualise of how all the tables link together i.e. the relationships etc... Is there any easy way without trawling through all the tables?

[Mav666]

In the first one:

FROM products AS op

 

can't be right, op should be orders_products

 

Further you need to add

 

LEFT JOIN " . TABLE_PRODUCTS . " AS p ON op.products_id = p.products_id

LEFT JOIN " . TABLE_ORDERS . " AS o ON op.orders_id = o.orders_id

 

and

 

WHERE o.date_purchased BETWEEN '" . $start_date . " 00:00:00' AND '" . $end_date . " 23:59:59'

 

to the first one and it should get you close. Don't think you need the time in there if you just specify the date it should be fine, as in

 

WHERE o.date_purchased BETWEEN '" . $start_date . "' AND '" . $end_date . "'

[zink]

In the first one:

FROM products AS op

 

can't be right, op should be orders_products

 

Further you need to add

 

LEFT JOIN " . TABLE_PRODUCTS . " AS p ON op.products_id = p.products_id

LEFT JOIN " . TABLE_ORDERS . " AS o ON op.orders_id = o.orders_id

 

If I were to change this would it still pull out all the other info like product name, model, price etc?

 

Thanks a lot for the help guys. I know as soon as I see the correct query I will be like "lol of course...!"

[Mav666]

It will retrieve what you tell it to in the SELECT part of the query - or SELECT * to retrieve everything. I am taking your word on them needing to be left joins, presumably you thought that part through. Basically the way to understand joins is to think of a base table you want to select, i.e. orders_products, from where you get a certain amount of info. Then you join that info with other tables that have the additional info you need. Does that make sense?