Thread: displaying correct name

Hi list!
I have two tables. One is contains User info, the other requesting info.
ownerid and buyerid are userid in the usertable.

        Table "users" Column   |            Type             |   
-----------+-----------------------------+userid    | integer                     | serialusername  | character
varying(25)      |password  | character varying(32)       |
 

         Table "item"    Column     |          Type          | 
----------------+------------------------+itemid         | integer                | serialuserid         | integer
         |title          | character varying(100) |
 

        Table "requests"
  Column   |            Type             |
------------+-----------------------------+requestid  | integer                     | serialownerid    | integer
            |buyerid    | integer                     |itemid     | integer                     |
 

what i want to do is select from "requests" where ownerid=<integer>.
the result should contain the users.username of requests.buyerid and the
item.title of requests.itemid.
my problem is... i am getting the username of requests.ownerid instead from my
query.
here is my query:

select u.username, i.title from test.requests r, test.users u, test.items i where r.ownerid=u.userid and
r.itemid=i.itemidand r.ownerid = (select userid from test.users where userid=1);
 
thanks.
any help appreciated.
 - bruce


__________________________________________________
Do you Yahoo!?
Yahoo! Web Hosting - establish your business online
http://webhosting.yahoo.com

Bruce Young wrote:
<cut>
> what i want to do is select from "requests" where ownerid=<integer>.
> the result should contain the users.username of requests.buyerid and the
> item.title of requests.itemid.
> my problem is... i am getting the username of requests.ownerid instead from my
> query.
> here is my query:
> 
> select u.username, i.title from test.requests r, test.users u, test.items i
>   where r.ownerid=u.userid and r.itemid=i.itemid
>   and r.ownerid = (select userid from test.users where userid=1);
>  
> thanks.
> any help appreciated.
> 
>   - bruce
???
As you answered yourself in description above, change
r.ownerid=u.userid into r.buyerid=u.userid

Regards,
Tomasz Myrta