Wednesday, March 28, 2012

Outer Join with 3 tables

Hope this is as easy as I think, but I am struggling to find answer in BOL,
etc.
I have 3 simple tables and want to link them on the same field, "ProductID".
The first table has all productid's on open SalesOrders and the qty sold.
The second table has productid's in Inventory for OnHand quanitity,
and a third table has productid's on PurchaseOrders for PurchaseOrder Qty.
I need to make sure all of the records in table one (SalesOrders) are
included regardless of the data in the other two tables. Additionally I wan
t
to return ONLY the ProductId's from the first table. LeftOuter Join isn't
working for me.
Obviously I am a newbie, and it's probably a dumb question, but you guys
always have the right answer really fast...Chuck,
I think that is equivalent to:
select ProductID from SalesOrders
AMB
"Chuck" wrote:

> Hope this is as easy as I think, but I am struggling to find answer in BOL
,
> etc.
> I have 3 simple tables and want to link them on the same field, "ProductID
".
> The first table has all productid's on open SalesOrders and the qty sold.
> The second table has productid's in Inventory for OnHand quanitity,
> and a third table has productid's on PurchaseOrders for PurchaseOrder Qty.
> I need to make sure all of the records in table one (SalesOrders) are
> included regardless of the data in the other two tables. Additionally I w
ant
> to return ONLY the ProductId's from the first table. LeftOuter Join isn't
> working for me.
> Obviously I am a newbie, and it's probably a dumb question, but you guys
> always have the right answer really fast...|||Sorry I was vague. I would like to return...
ProductID.SalesOrders
QtyOrdered.SalesOrders
QtyOnHand.Inventory
QtyOnPO.PurchaseOrders
returning all of the ProductID's and QtyOrdered in SalesOrders
and the quantities in Inventory and PurchaseOrders regardless of whether
that particular ProductID exists in that table (returning a null value if it
doesn't exist I presume).
In other words, two left outer joins to SalesOrders.
"Alejandro Mesa" wrote:
> Chuck,
> I think that is equivalent to:
> select ProductID from SalesOrders
>
> AMB
> "Chuck" wrote:
>|||Try,
select
so.ProductID,
so.QtyOrdered,
i.QtyOnHand,
po.QtyOnPO
from
SalesOrders as so
left join
Inventory as i
on so.ProductID = i.ProductID
left join
PurchaseOrders as po
on so.ProductID = po.ProductID
AMB
"Chuck" wrote:
> Sorry I was vague. I would like to return...
> ProductID.SalesOrders
> QtyOrdered.SalesOrders
> QtyOnHand.Inventory
> QtyOnPO.PurchaseOrders
> returning all of the ProductID's and QtyOrdered in SalesOrders
> and the quantities in Inventory and PurchaseOrders regardless of whether
> that particular ProductID exists in that table (returning a null value if
it
> doesn't exist I presume).
> In other words, two left outer joins to SalesOrders.
> "Alejandro Mesa" wrote:
>|||Thanks, That worked perfectly. For some reason I was getting an error when
attempting to create two outer joins in one query. Wonder why?
"Alejandro Mesa" wrote:
> Try,
> select
> so.ProductID,
> so.QtyOrdered,
> i.QtyOnHand,
> po.QtyOnPO
> from
> SalesOrders as so
> left join
> Inventory as i
> on so.ProductID = i.ProductID
> left join
> PurchaseOrders as po
> on so.ProductID = po.ProductID
>
> AMB
> "Chuck" wrote:
>sql

No comments:

Post a Comment