wood burning stoves*
The moose likes JDBC and the fly likes Correlation in SQL to distinguish tables Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Correlation in SQL to distinguish tables" Watch "Correlation in SQL to distinguish tables" New topic
Author

Correlation in SQL to distinguish tables

jite eghagha
Ranch Hand

Joined: Oct 06, 2006
Posts: 127
Hello,

If my query makes any sense...perhaps someone can explain how correlation affects it?

My SQL error message:

Tables or functions 'MainProduct' and 'MainProduct' have the same exposed names. Use correlation names to distinguish them.

My SQl Statement:

Select MainProduct.Title, MainProduct.Descr, MainProductType.Descr, NolaProductInfo.IERID, NolaSubBasin.Name, NolaVicinity.Name
From MainProduct, NolaProductInfo
LEFT JOIN NolaSubBasin on NolaProductInfo.SubBasinID = NolaSubBasin.ID
LEFT JOIN NolaVicinity on NolaProductInfo.VicinityID = NolaVicinity.ID
LEFT JOIN MainProduct on MainProductType.ProdTypeCode = MainProduct.ProdTypeCode
where(MainProduct.ProdId = 24350 And NolaProductInfo.ProdId = 24350)
[ March 10, 2008: Message edited by: jite eghagha ]
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30123
    
150

Jite,
The MainProduct table appears in your query twice. The message means to give each one a name: "From MainProduct product1" and reference the columns as "product1.Title".


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
jite eghagha
Ranch Hand

Joined: Oct 06, 2006
Posts: 127
thanks, but i get the same error:

The correlation name 'product1' has the same exposed name as table 'product1'.


assuming the sql below is correct:


Select MainProduct.Title, MainProduct.Descr, MainProductType.Descr, NolaProductInfo.IERID, NolaSubBasin.Name, NolaVicinity.Name
From MainProduct product1, MainProduct product2, NolaProductInfo
LEFT JOIN NolaSubBasin on NolaProductInfo.SubBasinID = NolaSubBasin.ID
LEFT JOIN NolaVicinity on NolaProductInfo.VicinityID = NolaVicinity.ID
LEFT JOIN product1 on MainProductType.ProdTypeCode = product2.ProdTypeCode
where(MainProduct.ProdId = 24350 And NolaProductInfo.ProdId = 24350)
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30123
    
150

Jite,
That's part of the way there. You need to use the product1/2 aliases in the select, not just the where clause. For example:


If the select refers to "MainProduct", the database doesn't know which of them you want.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Correlation in SQL to distinguish tables
 
Similar Threads
SQL command to show tables
Criteria Issue
Stumped on a SQL Statement
Difference between inner and outer joins
named sql-query dilema