This week's book giveaway is in the OO, Patterns, UML and Refactoring forum.
We're giving away four copies of Refactoring for Software Design Smells: Managing Technical Debt and have Girish Suryanarayana, Ganesh Samarthyam & Tushar Sharma on-line!
See this thread for details.
The moose likes JDBC and Relational Databases 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 and Relational Databases
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: 128
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
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 32481
    
214

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


[OCA 8 book] [Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Other Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, TOGAF part 1 and part 2
jite eghagha
Ranch Hand

Joined: Oct 06, 2006
Posts: 128
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
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 32481
    
214

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’ve looked at a lot of different solutions, and in my humble opinion Aspose is the way to go. Here’s the link: http://aspose.com
 
subject: Correlation in SQL to distinguish tables
 
It's not a secret anymore!