Joining the Same Table to Different Tables

0

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".

asked 27 Dec '17, 16:38

bendavid's gravatar image

bendavid
31●2●2●6
accept rate: 0%

edited 27 Dec '17, 16:42

Comment Text Removed
One Answer:
active answersoldest answersnewest answerspopular answers
0

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"

permanent link

answered 27 Dec '17, 17:30

Mark%20Culp's gravatar image

Mark Culp
24.9k●10●139●297
accept rate: 41%

edited 27 Dec '17, 20:19

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

(27 Dec '17, 17:37) bendavid
Replies hidden

Corrected: needed 'as' before dt. HTH

(27 Dec '17, 20:20) Mark Culp
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

learn more about Markdown

Question tags:

×3

question asked: 27 Dec '17, 16:38

question was seen: 1,186 times

last updated: 28 Dec '17, 14:16

Related questions

Illegal reference to correlation name

illegal reference to correlation name