Re: Newbie select question: How to get the same column twice?
Date: 1996/01/05
Message-ID: <30ED97CA.6D06_at_denitqm.mnet.uswest.com>#1/1
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.comDenver, CO 80202
Received on Fri Jan 05 1996 - 00:00:00 CET