Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2003
    Posts
    3

    Unanswered: help in getting data from multiple table joins

    Hi all,

    I'm using PostgreSQL database with the following tables:
    sales (
    invoice_no
    staff_no
    date_purchase timestamp
    total_purchase
    )

    sales_details (
    invoice_no
    inventory_no
    quantity_sold
    sold_price
    )

    returned (
    return_no
    date_returned
    invoice_no
    inventory_no
    quantity_returned
    )

    staff (
    staff_no
    name
    )

    The problem is, how do I get a listing of dates of the whole month, with the staff names, total sales for that date and total returned for that date.

    I'm able to do the above but without the total returned for the date. It's as:

    SELECT date(s.date_purchase) AS "Date Purchased", st.name AS "Staff Name", sum(s.total_purchase) AS "Amount sold" FROM sales s, staff st WHERE (s.staff_no = st.staff_no) GROUP BY date(s.date_purchase), st.name;

    But how do I do for the total returned (for that date and staff). I believe my trouble is that the returned table has no staff_no field and that makes it difficult...

    I'm open to using PL/PGSQL (i.e. using a stored procedure that returns a set of rows)

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Lightbulb

    You could join the sales.invoice_no with the returned invoice_no!

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Aug 2003
    Posts
    3
    Originally posted by LKBrwn_DBA
    You could join the sales.invoice_no with the returned invoice_no!

    In fact, I did that yesterday, as below:

    (SELECT date(s.date_purchase ) AS "Date", st.name AS "Staff name", sum(s.total_purchase ) AS "Total Sales", 0 AS "Total returned"
    FROM sales s, staff st
    WHERE (s.staff_no = st.staff_no )
    GROUP BY date(s.date_purchase ), st.name )
    UNION
    (
    SELECT r.date_return AS "Date", st.name AS "Staff name", 0 AS "Total Sales", sum(r.sold_price ) AS "Total returned"
    FROM returned r, staff st, sales s
    WHERE ((r.invoice_no = s.invoice_no ) AND (s.staff_no = st.staff_no ))
    GROUP BY r.date_return, st.name ));

    But the trouble is, I can't get a listing for each day. So if there's no sales or returned goods, then there's no entry for that. Furthermore, the above UNION SQL statement separate the "Total Sales" and "Total Returned" into 2 different rows.

  4. #4
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Lightbulb

    Originally posted by stelar
    In fact, I did that yesterday, as below:
    ...

    But the trouble is, I can't get a listing for each day. So if there's no sales or returned goods, then there's no entry for that. Furthermore, the above UNION SQL statement separate the "Total Sales" and "Total Returned" into 2 different rows.
    Try combining it like this:
    Code:
    
    SELECT DT AS "Date", name as "Staff name"
         , SUM(TotSales) AS "Total Sales"
         , SUM(TotRet)   AS "Total Returns"
    FROM
    (SELECT date(s.date_purchase ) Dt, st.name name
          , sum(s.total_purchase ) TotSales, 0 Totret
       FROM sales s, staff st 
      WHERE (s.staff_no = st.staff_no ) 
    UNION
     SELECT r.date_return, st.name, 0, sum(r.sold_price) 
       FROM returned r, staff st, sales s 
      WHERE ((r.invoice_no = s.invoice_no ) 
        AND (s.staff_no = st.staff_no )))
    GROUP BY dt, name; 
    
    Note: you do not need aliases on the UNION second select!

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

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