asked on

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?

ASKER CERTIFIED SOLUTION

Norush

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Kevin Cross

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Kevin Cross

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.

Kevin Cross

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

Kevin Cross

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.

Glenn Stearns

ASKER

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!