www.webdeveloper.com
Results 1 to 4 of 4

Thread: querying over multiple tables, help needed

  1. #1
    Join Date
    Mar 2006
    Posts
    413

    querying over multiple tables, help needed

    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.

  2. #2
    Join Date
    Mar 2006
    Posts
    52

    Lightbulb

    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

  3. #3
    Join Date
    Mar 2006
    Posts
    413
    ok i have this:

    Code:
    $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?

  4. #4
    Join Date
    Aug 2005
    Location
    The Garden State
    Posts
    5,634
    well in what area is the data wrong?
    Acceptable Use | SQL Forum FAQ | celery is tasteless | twitter

    celery is tasteless - currently needing some UI time

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles