querying over multiple tables, help needed
I have 4 tables like this:
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******************
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.
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 ...
ok i have this:
it outputs but the data is wrong, can anyone spot anything wrong?
$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";
well in what area is the data wrong?
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)