querying over multiple tables, help needed

[Sid3335]

Hi:

I have 4 tables like this:

**PRODUCTS**
product_ID
product_name

**BOOKINGS**
product_ID
transaction_ID

**BOOKING TRANSACTIONS**
transaction_ID
site_ID

**SITES**
site_ID


there are other fields in there but these are the important ones and it's pretty obvious how they are linked.

my question is:

I need to output a list of products sold for each site, like:

**************SITE NAME HERE******************
product1 8
product2 0
product3 3
product4 93

the problem is to get this information the SITE has to link to the BOOKING_TRANSACTIONS to get info on the pricing etc.
It then needs to look up each transaction in the BOOKINGS table to get other info.
Finally for each BOOKING it looks up ther product in the PRODUCTS table.

can anyone tell me how to get the information i need from this, even some psuedo code, so i can work it out for myself.

Thanks Guys, much appreciated.

[sacx13]

use select with Join.

is soemthing like

select s.*,b.*,b2.*,p* from SITES as s LEFT JOIN BOOKING_TRANSACTIONS as b on s.SITES_ID=b.SITES_ID LEFT JOIN BOOKINGS as b2 on b.transaction_ID=b2.transaction_ID left join PRODUCTS as p on p.product_ID=b2.product_ID;

anyway you will need to select the field what you want ...

Regards

[Sid3335]

ok i have this:

$query = "SELECT products.name, COUNT(products.id) as occurances, products.price, SUM(bookings_components.product_quantity) as product_quantity FROM
			products, bookings_components, bookings_transactions
			WHERE (products.id = bookings_components.product_id
			AND bookings_components.transaction_id = bookings_transactions.id
			AND bookings_transactions.site_id = " . $site . "
			AND bookings_transactions.date LIKE '2006-04-01%')
			GROUP BY products.name";

it outputs but the data is wrong, can anyone spot anything wrong?

[chazzy]

well in what area is the data wrong?