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.Consig nmentCode)
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:41Xpm, Lucas Kartawidjaja
<LucasKartawidj...@.discussions.microsoft.com> wrote:
> Without knowing the table structure and their relationship, I would suggest
> try the following query or something close:
> SELECT
> X X X X ConsignmentCodes.CodeDesc,
> X X X X Stockline.ConsignmentCode,
> X X X X Reserve.SalesOrderId,
> X X X X Invoice.ShipDate,
> X X X X Part.Number,
> X X X X Reserve.Shipped as Qty,
> X X X X SOItem.UnitAmount,
> X X X X (Reserve.Shipped*SOItem.UnitAmount) as Total,
> X X X X 1-(ConsignmentCodes.CodeCommRate*.01) as ConsignorPercent
> FROM X XStockline LEFT OUTER JOIN Part ON (Stockline.PartId=Part.PartId)
> X X X X LEFT OUTER JOIN Reserve ON (Stockline.StockId=Reserve.StockId)
> X X X X LEFT OUTER JOIN ConsignmentCodes ON
> X X X X X X X X (Stockline.ConsignmentCode=ConsignmentCodes.Consig nmentCode)
> X X X X LEFT OUTER JOIN SOItem ON (Stockline.PartId=SOItem.PartId)
> X X X X LEFT OUTER JOIN Invoice ON (Reserve.SalesOrderId=Invoice..SalesOrderId)
> X X X X AND (Reserve.InvoiceId=Invoice.InvoiceId)
> Lucas
>
> "Mehile.Orl...@.gmail.com" wrote:
>
>
>
> - Show quoted text -
Thanks, I think that gets me going again.
If I run into anything else, I'll repost.
Mo
sql