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

Jerry Bull <gbbull_at_denitqm.mnet.uswest.com> wrote:

>Paul Bramel wrote:
>>
>> 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.
>>
 

>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.

No need to join the table to itself. In fact, that will produce a cartesian product of the table emp against itself. Seems likely that Paul doesn't want exactly what he's asking for in his where clause. (Perhaps OR rather than AND; or only one or the other of the equations.)

Scott L. Harleman
harleman_at_spyder.net

--
Scott L. Harleman
harleman_at_spyder.net