Win a copy of Programmer's Guide to Java SE 8 Oracle Certified Associate (OCA) this week in the OCAJP forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Correlation in SQL to distinguish tables

 
jite eghagha
Ranch Hand
Posts: 128
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 34837
369
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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".
 
jite eghagha
Ranch Hand
Posts: 128
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 34837
369
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic