Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 301
  • Last Modified:

SELECT/JOIN Correlation Error

Here's the code:

SELECT armast.fcinvoice, armast.fcstatus, armast.flisprint, aritem.fitem,
  aritem.fpartno, armast.fnumber, armast.fcsource, armast.fsono,
  armast.finvtype, shmast.fshipno, shmast.fcstate
   FROM armast, aritem, shmast
LEFT JOIN shmast on armast.fnumber = shmast.fshipno
WHERE Armast.fcinvoice = Aritem.fcinvoice
AND Armast.fnumber = shmast.fshipno

Here's the error message:

The objects "shmast" and "shmast" in the FROM clause have the same exposed names. Use correlation names to distinguish them.

I looked at some answers on this error on this site, but am not having much luck getting my error resolved. Obviously, I'm not using alias names the right way when I try using them. How do I fix this one?

0
Glenn Stearns
Asked:
Glenn Stearns
  • 4
2 Solutions
 
NorushCommented:
Try with this
SELECT armast.fcinvoice, armast.fcstatus, armast.flisprint, aritem.fitem,
  aritem.fpartno, armast.fnumber, armast.fcsource, armast.fsono, 
  armast.finvtype, shmast.fshipno, shmast.fcstate 
   FROM armast, aritem
LEFT JOIN shmast on armast.fnumber = shmast.fshipno
WHERE Armast.fcinvoice = Aritem.fcinvoice
AND Armast.fnumber = shmast.fshipno

Open in new window

0
 
Kevin CrossChief Technology OfficerCommented:
Does that table need to be in your query twice for some reason?

armast.fnumber = shmast.fshipno
AND Armast.fnumber = shmast.fshipno

You seem to be joining the same way, so it going to be same record from same table twice.

Anyway, if you need this, you would do:

FROM shmast s1 LEFT JOIN shmast s2
ON s1.fshipno = s2.fshipno

For example.
0
 
Kevin CrossChief Technology OfficerCommented:
There was a lot of redundancy in the query, so I suspect you may have had a copy and paste mishap.  Happens all the time... Try looking at recreating the query cleanly.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Kevin CrossChief Technology OfficerCommented:
For example, did you mean something like this.  Think it is a better idea to spell out what kind of join to each table personally.
SELECT  armast.fcinvoice, 
		armast.fcstatus, 
		armast.flisprint, 
		aritem.fitem,
		aritem.fpartno, 
		armast.fnumber, 
		armast.fcsource, 
		armast.fsono, 
		armast.finvtype, 
		shmast.fshipno, 
		shmast.fcstate 
FROM (armast INNER JOIN aritem ON Armast.fcinvoice = Aritem.fcinvoice)
LEFT JOIN shmast ON armast.fnumber = shmast.fshipno

Open in new window

0
 
Kevin CrossChief Technology OfficerCommented:
Norush, just saw your post.  Think you saw the same thing that the shmast table was duplicated and taking out will fix unless glennes needed two instances for some reason.
0
 
Glenn StearnsAnalystAuthor Commented:
Thanks, guys. I think it's time to go on home for the week. Clearly, the company isn't getting the intelligence from me that they're paying for today!
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now