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

SELECT  A.name ASSET_NAME, 
        B.name ASSET_OWNER,
        B.name ASSET_USER 
FROM
        emp  A, 
        asset B

must do the job.

Here for each tuple in the cross-product for asset X emp, you pick only those tuples which have the emp.id field in the set of tuples from asset which have the owner and the user identical. This will be slow as this is a correlated subquery. I expect the subquery to be evaluated once (???).

Instead try the following:

SELECT asset.name ASSET_NAME,
       emp.name   ASSET_OWNER,
       emp.name   ASSET_USER,
FROM
       emp,
       asset
WHERE 
       asset.user = asset.owner
       AND
       asset.owner = emp.id

firstly verify if :

  SELECT owner FROM asset WHERE owner = user

returns any rows ...

(mind u, my SQL is not too good -- I am just using set theory here!!!).        Received on Fri Jan 05 1996 - 00:00:00 CET