CODEWITHSUNDEEP
sqlsql-server
Eddi
Eddi

Reputation: 811

SQL Server: use table with duplicate columns names

I am trying to create a subselect on a SQL query like this:

SELECT 
    t0.id, t0.gad, t0.gbd, t0.mandant, t0.gbd, t1.year 
FROM 
    person t0 
LEFT OUTER JOIN 
    control t1 ON (t0.id = t1.id)  
WHERE 
    ((t0.id = '1') or (t0.id = '2'));

You can see that there are two identical columns gbd. However, this query runs successfully. But when I try to use this query as a subselect like below, it fails, returning the error #8156:

SELECT * 
FROM
    (SELECT 
         t0.id, t0.gad, t0.gbd, t0.mandant, t0.gbd, t1.year 
     FROM 
         person t0 
     LEFT OUTER JOIN 
         control t1 ON (t0.id = t1.id)  
     WHERE ((t0.id = '1') or (t0.id = '2'))) result 
WHERE 
    (result.gad >= 0) and (result.gbd <= 99);

I know that there is actually no sense in selecting the same column twice but I can't change the inner SQL query because I just read it out of a database.

The goal of this query is to just get results in a specific range.

So my question is, how can I make this query work without changing the inner SELECT statement? This also means that I don't know about the columns, so I am not able to change the * into the specific column names.

Upvotes: 0

Views: 627

Answers (3)

Alexey
Alexey

Reputation: 919

You can modify the subquery after you have read it from the database to assign aliases as suggested by others. Or you can try this without modifying the subquery if you can rely that the subquery will always have the same set of columns in the same order:

SELECT * 
FROM
    (SELECT 
         t0.id, t0.gad, t0.gbd, t0.mandant, t0.gbd, t1.year 
     FROM 
         person t0 
     LEFT OUTER JOIN 
         control t1 ON (t0.id = t1.id)  
     WHERE ((t0.id = '1') or (t0.id = '2'))) result (id, gad, gbd1, mandant, gbd2, year)
WHERE 
    (result.gad >= 0) and (result.gbd1 <= 99);

Upvotes: 0

Vignesh Kumar A
Vignesh Kumar A

Reputation: 28403

Your Subquery has duplicate name. use alias to differenciate

SELECT * FROM
(
   SELECT t0.id, t0.gad, t0.gbd, t0.mandant, t0.gbd Tmp, t1.year 
   FROM person t0 LEFT OUTER JOIN control t1 ON (t0.id = t1.id)  
   WHERE ((t0.id='1') or (t0.id='2'))
) result 
WHERE (result.gad >= 0) and (result.gbd <= 99);

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269953

You need to give it an alias. A subquery cannot have two columns with the same name:

SELECT *
FROM (SELECT t0.id, t0.gad, t0.gbd as gbd, t0.mandant, t0.gbd as gbd1, t1.year 
      FROM person t0 LEFT OUTER JOIN control t1 ON (t0.id = t1.id)  
      WHERE ((t0.id='1') or (t0.id='2'))
     ) result 
WHERE (result.gad >= 0) and (result.gbd <= 99);

In other words, you cannot get the query to work as a subquery, because it does not meet the requirements for a subquery.

Upvotes: 2

Related Questions