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

Paul Bramel 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
>
> I've tried many variations on the above statement with no luck. Any
> help would be appreciated.
>
> Thanks,
>
> Paul Bramel
 

This will only return rows if the user and owner are the same. To reference different users and owners use the following:

  SELECT asset.name, e1.name Owner, e2.name user_name

         FROM emp e1, asset, emp e2
         WHERE asset.owner = e1.id
         AND asset.usr = e2.id

Jerry.

Jerry Bull,  Sr. MTS			voice: (303) 624-3073
U S WEST Information Technologies	fax:   (303) 624-8462 
931 14th Street, Suite 920	email: gbbull_at_uswest.com