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.
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 ...
$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?