BorlandTalk.com

Shawn Thomas · Guest

Hi,

I'm starting to work with an MSSQL 2000 server under Delphi 7. I need to
pull different fields from 5 different tables. I constructed the following
SQL statement to do it:

SELECT DISTINCT
Nxt_InterFace_Purchasing.ItemID, Nxt_InterFace_Purchasing.Processed,
Nxt_InterFace_Purchasing.UpdateAction,
Nxt_InterFace_Purchasing.TransRefID,
Nxt_InterFace_Purchasing.DataAreaID, Nxt_InterFace_Purchasing.RecID,
Nxt_InterFace_Purchasing.InventTransRecID,
Nxt_InterFace_Purchasing.ItemGroupID, Nxt_InterFace_Purchasing.CreatedDate,
Nxt_Interface_Purchasing.CreatedTime,
InventTrans.Qty, InventTrans.DatePhysical, InventTrans.DateExpected,
InventTrans.DateStatus, InventTrans.StatusReceipt,
InventTrans.CustVendAC,
VendTable.AccountNum, VendTable.Name,
PurchLine.Bsc_item_length, Purchline.bsc_item_width,
PurchLine.PurchPrice, PurchLine.InventDimID,
InventDim.InventSerialID, InventDim.InventLocationID,
InventDim.InventBatchID
FROM Nxt_Interface_Purchasing, InventTrans
RIGHT OUTER JOIN VendTable ON VendTable.AccountNum =
InventTrans.CustVendAC
RIGHT OUTER JOIN PurchLine ON PurchLine.InventTransID =
InventTrans.InventTransID
RIGHT OUTER JOIN InventDim ON InventDim.InventDimID =
PurchLine.InventDimID
WHERE (Nxt_Interface_Purchasing.InventTransRecid = InventTrans.RecID)
AND (Nxt_Interface_Purchasing.Processed = 0)
AND (Nxt_Interface_Purchasing.ItemGroupId = 'BELT')

The problem is there is only one record in the Nxt_Interface_Purchasing
table and my query returns 2. I think the problem is on the last RIGHT
OUTER join to the InventDim table, but I don't know what it should be. Can
someone tell me if that is where my problem is or am I doing something else
wrong? Thanks.

ST

Larry Carter · Guest

Without actual data it is had to tell but I would suspect one of two things.

1. Table InventTrans has two records with the same RecID as the record in
Nxt_InterFace_Purchasing. This is unlikely, as I would expect RecID to be
unique but I wanted to present it as a possibility.

2. One of the Right joined tables has more than one record that manages to
force an additional row join. Remember that RIGHT OUTER JOIN stipulates
that the right hand side of the join (i.e. the new table you are joining)
has all of its records returned regardless of existing, joinable records in
the left hand side (the table you are joining to). Personally I almost
never use RIGHT OUTER joins as I consider them a pain to read but sometimes
they are the right choice. In considering the set building that occurs with
this query, you have to start with the final RIGHT join and then consider
the inner (straight) join to figure out what kind of result set you are
going to get.

Larry

"Shawn Thomas" <sthomas77 (AT) swbell (DOT) net> wrote