Joining the Same Table to Different Tables

[Former Member]

We have a Vendor table, an Inventory table and a Worksheet table set up. The Inventory table has a column that stores the default vendor for an item, the worksheet table has a column that stores the vendor that is used on a particular order.

I'm attempting to pull two columns - one which displays the default vendor for an item, and one which pulls the vendor the item is ordered with from the worksheet table. I've tried a number of things with aliases to accomplish this but nothing is working. So essentially the final data would have three columns: Item ID, Default Vendor (the vendor name from the vendor table when joined with the Inventory table), Receiving Vendor (the vendor name from the vendor table when joined with the worksheet table).

The following is one of my more recent attempts at accomplishing this. If anyone has any ideas on how to do this or can indicate a great tutorial on how to do it it would be appreciated!

ALTER VIEW "eDatabase"."00_Test3_BD"( /* view_column_name, ... */ )
AS
SELECT "eDatabase"."Vendor"."VEN_Code",
"eDatabase"."Vendor"."VEN_CompanyName"
FROM ( "eDatabase"."Vendor" JOIN "eDatabase"."StockInventoryLocal" As "DefaultVendor" ON "eDatabase"."Vendor"."VEN_PK" = "eDatabase"."StockInventoryLocal"."SIL_VEN_FK_Default" ) JOIN "eDatabase"."ReceiveWorksheet" As "WorksheetVendor"  ON "eDatabase"."Vendor"."VEN_PK" = "eDatabase"."ReceiveWorksheet"."RCW_VEN_FK"

With this I am receiving an error "Correlation name 'StockInventoryLocal' not found".

[MarkCulp · Accepted solution]

I believe the issue is that you have aliased the StockInventoryLocal table as DefaultVemdor. Also, the use of eDatabase is not needed (SQLA ignores it!)

Try rewriting your query as:

ALTER VIEW "00_Test3_BD"( / view_column_name, ... / )
AS
SELECT dt."VEN_Code",
       dt."VEN_CompanyName"
FROM ( "Vendor"
       JOIN "StockInventoryLocal" As "DefaultVendor"
         ON "Vendor"."VEN_PK" = "DefaultVendor"."SIL_VEN_FK_Default" ) as dt
 JOIN "ReceiveWorksheet" As "WorksheetVendor"
   ON dt."VEN_PK" = "WorksheetVendor"."RCW_VEN_FK"

[Former Member · Accepted solution]

Thanks for your response, Mark. I'm getting a syntax error "near dt" on line 7 with that implementation.

[MarkCulp · Accepted solution]

Corrected: needed 'as' before dt. HTH