Re: Newbie select question: How to get the same column twice?

Paul Bramel <paulbr_at_comm.mot.com> wrote:

>I very new to SQL and could use some help with a select statement. How
>do I select the same column twice from one select statement?
 

>Here is a simple example:
 

>Table EMP:
> Name Null? Type
> ------------------------------- -------- ----
> ID NOT NULL NUMBER
> NAME VARCHAR2(10)
 
>Table ASSET:
> Name Null? Type
> ------------------------------- -------- ----
> NAME VARCHAR2(20)
> OWNER NUMBER
> USR NUMBER
 
>where owner and usr are both foreign keys referencing EMP.ID.
 

>How do I select the asset name, owner name, and user name in one
>select statement? The following returns no rows.
 

>SELECT asset.name, emp.name Owner, emp.name user_name
> FROM emp, asset
> WHERE asset.owner = emp.id
> AND asset.usr = emp.id

If you are getting no rows returned, it is because you have no rows in table ASSET whose column OWNER = column USER. Your WHERE clause is requesting all rows returned where asset.owner = emp.id and asset.usr = emp.id. In other words, all three columns must would have to be identical for a row to be returned.

What you must do depends on what you want to query. Do you want rows returned for any asset/emp record pairing "where asset.owner = emp.id OR asset.usr = emp.id"? If so, word you where clause just like that. Or, perhaps you only intended for asset.owner and emp.id to be compared. In that case, leave off the "and asset.usr = emp.id".

At any rate, in direct answer to your question "How to get the same column twice?", you already have that part exactly right. The following SQL statement:

  select emp.name Owner,

            emp.name user_name
  from emp;

would return the same column twice. Returning the same column twice is accomplished by specifying it twice in the column list, not by anything done in the where clause.

Scott L. Harleman
harleman_at_spyder.net

--
Scott L. Harleman
harleman_at_spyder.net