Newbie query question

I need to include data from a table which is not directly related to
the "hub" table, and am kinda stuck.
I doubted it would work (tried anyhow), but this is the code from my
attempt:
SELECT
ConsignmentCodes.CodeDesc,
Stockline.ConsignmentCode,
Reserve.SalesOrderId,
Invoice.ShipDate,
Part.Number,
Reserve.Shipped as Qty,
SOItem.UnitAmount,
(Reserve.Shipped*SOItem.UnitAmount) as Total,
1-(ConsignmentCodes.CodeCommRate*.01) as ConsignorPercent
FROM Stockline JOIN Part ON (Stockline.PartId=Part.PartId)
LEFT JOIN Reserve ON (Stockline.StockId=Reserve.StockId)
LEFT JOIN ConsignmentCodes ON
(Stockline.ConsignmentCode=ConsignmentCodes.ConsignmentCode)
LEFT JOIN SOItem ON (Stockline.PartId=SOItem.PartId)
WHERE Reserve.SalesOrderId=Invoice.SalesOrderId
AND Reserve.InvoiceId=Invoice.InvoiceId
You can see why it did didn't work, and hopefully, what I am trying to
do.
I need "Invoice.ShipDate", but "Invoice" doesn't have any fields which
relate to "Stockline".
If I try to base all my relationships on "Resreve", then I end up with
ths same problem for "Part".
What's my next step?
-MoWithout knowing the table structure and their relationship, I would suggest
try the following query or something close:
SELECT
ConsignmentCodes.CodeDesc,
Stockline.ConsignmentCode,
Reserve.SalesOrderId,
Invoice.ShipDate,
Part.Number,
Reserve.Shipped as Qty,
SOItem.UnitAmount,
(Reserve.Shipped*SOItem.UnitAmount) as Total,
1-(ConsignmentCodes.CodeCommRate*.01) as ConsignorPercent
FROM Stockline LEFT OUTER JOIN Part ON (Stockline.PartId=Part.PartId)
LEFT OUTER JOIN Reserve ON (Stockline.StockId=Reserve.StockId)
LEFT OUTER JOIN ConsignmentCodes ON
(Stockline.ConsignmentCode=ConsignmentCodes.ConsignmentCode)
LEFT OUTER JOIN SOItem ON (Stockline.PartId=SOItem.PartId)
LEFT OUTER JOIN Invoice ON (Reserve.SalesOrderId=Invoice.SalesOrderId)
AND (Reserve.InvoiceId=Invoice.InvoiceId)
Lucas
"Mehile.Orloff@.gmail.com" wrote:
> I need to include data from a table which is not directly related to
> the "hub" table, and am kinda stuck.
> I doubted it would work (tried anyhow), but this is the code from my
> attempt:
> SELECT
> ConsignmentCodes.CodeDesc,
> Stockline.ConsignmentCode,
> Reserve.SalesOrderId,
> Invoice.ShipDate,
> Part.Number,
> Reserve.Shipped as Qty,
> SOItem.UnitAmount,
> (Reserve.Shipped*SOItem.UnitAmount) as Total,
> 1-(ConsignmentCodes.CodeCommRate*.01) as ConsignorPercent
> FROM Stockline JOIN Part ON (Stockline.PartId=Part.PartId)
> LEFT JOIN Reserve ON (Stockline.StockId=Reserve.StockId)
> LEFT JOIN ConsignmentCodes ON
> (Stockline.ConsignmentCode=ConsignmentCodes.ConsignmentCode)
> LEFT JOIN SOItem ON (Stockline.PartId=SOItem.PartId)
> WHERE Reserve.SalesOrderId=Invoice.SalesOrderId
> AND Reserve.InvoiceId=Invoice.InvoiceId
> You can see why it did didn't work, and hopefully, what I am trying to
> do.
> I need "Invoice.ShipDate", but "Invoice" doesn't have any fields which
> relate to "Stockline".
> If I try to base all my relationships on "Resreve", then I end up with
> ths same problem for "Part".
>
> What's my next step?
> -Mo
>|||On Feb 22, 2:41=A0pm, Lucas Kartawidjaja
<LucasKartawidj...@.discussions.microsoft.com> wrote:
> Without knowing the table structure and their relationship, I would sugges=t
> try the following query or something close:
> SELECT
> =A0 =A0 =A0 =A0 ConsignmentCodes.CodeDesc,
> =A0 =A0 =A0 =A0 Stockline.ConsignmentCode,
> =A0 =A0 =A0 =A0 Reserve.SalesOrderId,
> =A0 =A0 =A0 =A0 Invoice.ShipDate,
> =A0 =A0 =A0 =A0 Part.Number,
> =A0 =A0 =A0 =A0 Reserve.Shipped as Qty,
> =A0 =A0 =A0 =A0 SOItem.UnitAmount,
> =A0 =A0 =A0 =A0 (Reserve.Shipped*SOItem.UnitAmount) as Total,
> =A0 =A0 =A0 =A0 1-(ConsignmentCodes.CodeCommRate*.01) as ConsignorPercent
> FROM =A0 =A0Stockline LEFT OUTER JOIN Part ON (Stockline.PartId=3DPart.Par=tId)
> =A0 =A0 =A0 =A0 LEFT OUTER JOIN Reserve ON (Stockline.StockId=3DReserve.St=ockId)
> =A0 =A0 =A0 =A0 LEFT OUTER JOIN ConsignmentCodes ON
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 (Stockline.ConsignmentCode=3DConsignmentCo=des.ConsignmentCode)
> =A0 =A0 =A0 =A0 LEFT OUTER JOIN SOItem ON (Stockline.PartId=3DSOItem.PartI=d)
> =A0 =A0 =A0 =A0 LEFT OUTER JOIN Invoice ON (Reserve.SalesOrderId=3DInvoice=.SalesOrderId)
> =A0 =A0 =A0 =A0 AND (Reserve.InvoiceId=3DInvoice.InvoiceId)
> Lucas
>
> "Mehile.Orl...@.gmail.com" wrote:
> > I need to include data from a table which is not directly related to
> > the "hub" table, and am kinda stuck.
> > I doubted it would work (tried anyhow), but this is the code from my
> > attempt:
> > SELECT
> > ConsignmentCodes.CodeDesc,
> > Stockline.ConsignmentCode,
> > Reserve.SalesOrderId,
> > Invoice.ShipDate,
> > Part.Number,
> > Reserve.Shipped as Qty,
> > SOItem.UnitAmount,
> > (Reserve.Shipped*SOItem.UnitAmount) as Total,
> > 1-(ConsignmentCodes.CodeCommRate*.01) as ConsignorPercent
> > FROM Stockline JOIN Part ON (Stockline.PartId=3DPart.PartId)
> > LEFT JOIN Reserve ON (Stockline.StockId=3DReserve.StockId)
> > LEFT JOIN ConsignmentCodes ON
> > (Stockline.ConsignmentCode=3DConsignmentCodes.ConsignmentCode)
> > LEFT JOIN SOItem ON (Stockline.PartId=3DSOItem.PartId)
> > WHERE Reserve.SalesOrderId=3DInvoice.SalesOrderId
> > AND Reserve.InvoiceId=3DInvoice.InvoiceId
> > You can see why it did didn't work, and hopefully, what I am trying to
> > do.
> > I need "Invoice.ShipDate", but "Invoice" doesn't have any fields which
> > relate to "Stockline".
> > If I try to base all my relationships on "Resreve", then I end up with
> > ths same problem for "Part".
> > What's my next step?
> > -Mo- Hide quoted text -
> - Show quoted text -
Thanks, I think that gets me going again.
If I run into anything else, I'll repost.
Mo