Results 1 to 15 of 15
  1. #1
    Join Date
    Feb 2004
    Posts
    10

    Question Unanswered: Outer Join Problem

    I am trying to gather two sets of data for a form. I want distinct values from the pricebook.productid and pricebook.productname to be displayed in two columns and the results of the cast statement in a third. problem is the cast statement may not contain all of the productid's or productnames from the first two columns but i still need them displayed with a zero in the third.....

    Can anyone steer me in the right direction?


    SELECT pricebook.productid, pricebook.productname, CAST(ROUND(SUM(PRODQTY), 2) AS Decimal(10, 2))
    FROM ticket left outer JOIN
    pricebook ON TICKET.Plantid = pricebook.pricegroupid
    WHERE (TICKET.RUNDATE = '2004-02-17') AND (pricebook.pricegroupid = '00045') AND (pricebook.Productid != '888')
    GROUP BY ticket.PRODID, pricebook.PRODuctid, pricebook.productname
    ORDER BY pricebook.productid

  2. #2
    Join Date
    Feb 2004
    Location
    Rhode Island
    Posts
    69

    Re: Outer Join Problem

    Originally posted by hellhound
    I am trying to gather two sets of data for a form. I want distinct values from the pricebook.productid and pricebook.productname to be displayed in two columns and the results of the cast statement in a third. problem is the cast statement may not contain all of the productid's or productnames from the first two columns but i still need them displayed with a zero in the third.....

    Can anyone steer me in the right direction?


    SELECT pricebook.productid, pricebook.productname, CAST(ROUND(SUM(PRODQTY), 2) AS Decimal(10, 2))
    FROM ticket left outer JOIN
    pricebook ON TICKET.Plantid = pricebook.pricegroupid
    WHERE (TICKET.RUNDATE = '2004-02-17') AND (pricebook.pricegroupid = '00045') AND (pricebook.Productid != '888')
    GROUP BY ticket.PRODID, pricebook.PRODuctid, pricebook.productname
    ORDER BY pricebook.productid
    Use ISNULL

  3. #3
    Join Date
    Feb 2004
    Posts
    10

    Re: Outer Join Problem

    Could you elaborate? Something like this?

    SELECT pricebook.productid, pricebook.productname,
    (select distinct isnull(CAST(ROUND(SUM(PRODQTY), 2) AS Decimal(10, 2)), '0') as SoldTons
    from ticket WHERE (RUNDATE = '2004-02-17') AND (plantid = '00045') AND (prodid != '888')
    group by prodid)
    FROM ticket full JOIN
    pricebook ON TICKET.Plantid = pricebook.pricegroupid
    WHERE (TICKET.RUNDATE = '2004-02-17') AND(pricebook.pricegroupid = '00045') AND (pricebook.Productid != '888')
    GROUP BY pricebook.PRODuctid, pricebook.productname
    ORDER BY pricebook.productid


    Originally posted by smasanam
    Use ISNULL

  4. #4
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Select CAST(ISNULL(null, 0) AS Decimal(10, 2))

    SELECT pricebook.productid, pricebook.productname,
    CAST(ISNULL(ROUND(SUM(PRODQTY), 2), 0) AS Decimal(10, 2))
    FROM ticket left outer JOIN
    pricebook ON TICKET.Plantid = pricebook.pricegroupid
    WHERE (TICKET.RUNDATE = '2004-02-17') AND (pricebook.pricegroupid = '00045') AND (pricebook.Productid != '888')
    GROUP BY ticket.PRODID, pricebook.PRODuctid, pricebook.productname
    ORDER BY pricebook.productid
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  5. #5
    Join Date
    Feb 2004
    Posts
    10
    I don't think I am being clear enough. Forgive me....

    Basically i have data in two tables, one contains the prodid and name, the other contains the prodid and qty. i want one table with all the prodid's and names combined with the qty values from the other. Not all prodid's will have qty data, so i need those as '0'.......

    clear as mud?

    thanks for your help.

    Originally posted by r123456
    Select CAST(ISNULL(null, 0) AS Decimal(10, 2))

    SELECT pricebook.productid, pricebook.productname,
    CAST(ISNULL(ROUND(SUM(PRODQTY), 2), 0) AS Decimal(10, 2))
    FROM ticket left outer JOIN
    pricebook ON TICKET.Plantid = pricebook.pricegroupid
    WHERE (TICKET.RUNDATE = '2004-02-17') AND (pricebook.pricegroupid = '00045') AND (pricebook.Productid != '888')
    GROUP BY ticket.PRODID, pricebook.PRODuctid, pricebook.productname
    ORDER BY pricebook.productid

  6. #6
    Join Date
    Feb 2004
    Location
    Rhode Island
    Posts
    69
    Originally posted by hellhound
    I don't think I am being clear enough. Forgive me....

    Basically i have data in two tables, one contains the prodid and name, the other contains the prodid and qty. i want one table with all the prodid's and names combined with the qty values from the other. Not all prodid's will have qty data, so i need those as '0'.......

    clear as mud?

    thanks for your help.
    If you mean

    Product Id Product Name, QTY
    ------------------------------------
    1 abc 100
    2 efg 50
    3 effff NULL

    And QTY is in the 2nd Table then I think r123456's Query should work. If not

    Can you please give more explanation

  7. #7
    Join Date
    Feb 2004
    Posts
    10
    Originally posted by smasanam
    If you mean

    Product Id Product Name, QTY
    ------------------------------------
    1 abc 100
    2 efg 50
    3 effff NULL

    And QTY is in the 2nd Table then I think r123456's Query should work. If not

    Can you please give more explanation
    ok you are very close there, but when I run r123456's Query, i get multiple Productid and product name values.

    ex.

    Product Id Product Name, QTY
    ------------------------------------
    1 abc 100
    1 abc 50
    1 abc NULL
    2 efg 100
    2 efg 50
    2 efg NULL
    3 effff 100
    3 effff 50
    3 effff NULL

  8. #8
    Join Date
    Feb 2004
    Location
    Rhode Island
    Posts
    69
    Can you sum them up???

    Or you need induvidual records??

  9. #9
    Join Date
    Feb 2004
    Location
    Rhode Island
    Posts
    69
    Can you please tell us the relationship between those 2 tables??

  10. #10
    Join Date
    Feb 2004
    Posts
    10
    Originally posted by smasanam
    Can you please tell us the relationship between those 2 tables??
    Here is an example of the table setup.

    pricebook

    productid productname
    -------------------------
    abc abc product
    def def product
    efff efff product


    Ticket


    Prodid qty
    ---------------------------------
    abc 20
    abc 100
    abc 50
    def 150
    def 200


    Here is what I need from these;

    Productid Productname QTY
    ----------------------------------------------
    abc abc product 170
    def def product 350
    efff efff product 0

    Make any better sense?

    i only need one distinct listing for the productid as determined by the where clause, and a sum for the qty.... or 0 if it is not in the qty table.
    Last edited by hellhound; 02-19-04 at 18:53.

  11. #11
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    The join condition between the two tables is not clear. In your example, Pricebook.ProductID = 'abc abc', however Ticket.productID = 'abc'.

    select pb.productID, sum(NVL(t.QTY, 0))
    from priceBook pb
    LEFT OUTER JOIN
    ticket t ON
    pb.productID = t.productID;
    group by pb.productid;
    Last edited by r123456; 02-19-04 at 21:04.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  12. #12
    Join Date
    Feb 2004
    Posts
    10
    Originally posted by r123456
    The join condition between the two tables is not clear. In your example, Pricebook.ProductID = 'abc abc', however Ticket.productID = 'abc'.

    select pb.productID, sum(NVL(t.QTY, 0))
    from priceBook pb
    LEFT OUTER JOIN
    ticket t ON
    pb.productID = t.productID;
    group by pb.productid;
    now that i look back that was confusing;;;

    tblpricebook

    productid | productname
    ----------------------------------------------------------------
    abc | abc product
    def | def product
    efff | efff product


    tblTicket


    Prodid | qty
    --------------------------------------------------------------------
    abc | 20
    abc | 100
    abc | 50
    def | 150
    def | 200


    Here is what I need from these;

    Result

    Productid | Productname | QTY
    ------------------------------------------------------------------------------
    abc | abc product | 170
    def | def product | 350
    efff | efff product | 0


    Basically some productid appears multiple times with differing qty's in the ticket tbl. All productid's appear once in the pricebook table.
    I need all the productid's from pricebook listed with appropriate sums from the ticket table and where there are no sums from the ticket table a null or zero int the result.

    I really appreciate your patience on this.
    Last edited by hellhound; 02-19-04 at 21:55.

  13. #13
    Join Date
    Feb 2004
    Posts
    10
    OK, this works....

    select distinct pb.productid, isnull(sum(t.prodQTY), 0) as SOLDTONS
    from priceBook pb left outer JOIN
    ticket t ON
    pb.productID = t.prodID
    where pb.pricegroupid = '00045' /*and t.rundate = '2004-02-18'*/
    group by pb.productid

    only problem is if i narrow down the selection with the rundate in the where clause, I then limit my results to only what is contained in the ticket table.....

    Is Having my answer?

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    move the condition on the t column into the ON clause for the left outer join

    oh, and you do not need DISTINCT when you use GROUP BY, groups are distinct by definition:
    PHP Code:
    select pb.productid
         
    isnull(sum(t.prodQTY), 0) as SOLDTONS
      from priceBook pb 
    left outer 
      join ticket t 
        on pb
    .productID t.prodID
       
    and                t.rundate '2004-02-18'  
     
    where pb.pricegroupid '00045' 
    group 
        by pb
    .productid 
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Feb 2004
    Posts
    10
    Originally posted by r937
    move the condition on the t column into the ON clause for the left outer join

    oh, and you do not need DISTINCT when you use GROUP BY, groups are distinct by definition:
    PHP Code:
    select pb.productid
         
    isnull(sum(t.prodQTY), 0) as SOLDTONS
      from priceBook pb 
    left outer 
      join ticket t 
        on pb
    .productID t.prodID
       
    and                t.rundate '2004-02-18'  
     
    where pb.pricegroupid '00045' 
    group 
        by pb
    .productid 

    EXCELLENT! Thanks for all your help!

    mb

Posting Permissions

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