Monday, March 26, 2012

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.productidOriginally 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|||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|||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|||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|||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|||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|||Can you sum them up?

Or you need induvidual records??|||Can you please tell us the relationship between those 2 tables??|||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.|||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;|||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.|||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?|||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: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|||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: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

No comments:

Post a Comment