Menu
I need a join that yields three fields with the same name from two different tables. When I try to run my sql query, VS gives me the following error.
The objects "PoliticalFigures" and "PoliticalFigures" in the FROM clause have the same exposed names. Use correlation names to distinguish them.
I've been trying to use "AS" to distinguish these fields, but I haven't found a working solution. This is the sql query I'm running:
SELECT Countries.Name AS Country, PoliticalFigures.Name AS President, PoliticalFigures.Name AS VicePresident FROM Countries LEFT OUTER JOIN PoliticalFigures ON Countries.President_Id = PoliticalFigures.Id LEFT OUTER JOIN PoliticalFigures ON Countries.VicePresident_Id = PoliticalFigures.Id If it's not obvious from the code, these are the tables.
(Note, the tables and fields in my application have different names. I am generalizing them to make this example clearer and hopefully more relevant to others.)
(The tools I'm using are Visual Web Developer 2010 Express and SQL Server 2008 Express.)
769
A correlation name can be defined in the FROM clause of a query and after the name of the target table or view in an UPDATE, MERGE, or DELETE statement. With Z defined as a correlation name for table X. MYTABLE, only Z should be used to qualify a reference to a column of X.
SQL JOIN. A JOIN clause is used to combine rows from two or more tables, based on a related column between them. Notice that the "CustomerID" column in the "Orders" table refers to the "CustomerID" in the "Customers" table. The relationship between the two tables above is the "CustomerID" column.
A conditional column join is a fancy way to let us join to a single column and to two (or more) columns in a single query. We can accomplish this by using a case statement in the on clause of our join. A case statement allows us to test multiple conditions (like an if/else if/else) to produce a single value.
The LEFT JOIN command returns all rows from the left table, and the matching rows from the right table. The result is NULL from the right side, if there is no match.
Correlation name are temporary table names which are used in FROM clause. It is used when your table name is long and it makes statement very long and confusing. Correlation name makes statement easy to read. If you are creating correlation name of any table then it is must to use the correlation name of table.
A correlation name can be defined in the FROM clause of a query and after the name of the target table or view in an UPDATE, MERGE, or DELETE statement. With Z defined as a correlation name for table X.MYTABLE, only Z should be used to qualify a reference to a column of X.MYTABLE in that SELECT statement.
When you use the OPENROWSET () function with the BULK option, you must provide a correlation name (also known as a range variable or alias) in the FROM clause. To fix this error, simply provide a correlation name/alias for your query.
The Pearson Correlation Coefficient Formula in SQL. This coefficient is calculated as a number between -1 and 1. 1 being the strongest possible positive correlation and -1 being the strongest possible negative correlation. A positive correlation means that as one number increases the second number will also increase.
Use table aliases for each reference to PoliticalFigures instead:
SELECT Countries.Name AS Country, P.Name AS President, VP.Name AS VicePresident FROM Countries LEFT OUTER JOIN PoliticalFigures AS P ON Countries.President_Id = P.Id LEFT OUTER JOIN PoliticalFigures AS VP ON Countries.VicePresident_Id = VP.Id If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
