grabbing coreect details 1

[coolicus]

I am using asp to gather details form a database but am unsure of how to go about doing what I need.

The database has columns including

id, name, address, refid

The id is a unique field to each row, the refid is a number of the id field related to that row.

For example I may have one user

1, Bob, UK, 6

And another linked to that user

2, Mr Linked, UK, 1

I will have the id of Mr Linked so can grab his refid

SELECT refid FROM t_users where id = '" & session("sessionid") & "'"

I also need to grab the name of his refid link and display on page so I would need to output 'Bob'. can I do this with the one query or would I need to grab the refid into a variable then use another query to access the database again and grab the name of 'Bob'?

 

[mbiro]

SELECT t_users_1.name
FROM t_users INNER JOIN
t_users AS t_users_1 ON t_users.refid = t_users_1.id
WHERE (t_users.id = 2)

 

[coolicus]

Thanks, it worked with no problems. It was the AS part that I needed mostly, I had done inner joins before not just on the same table.

 

[Tarwn]

You may want to change that to a LEFT JOIN rather than an INNER JOIN so that you don't filter out records that have a NULL refid

-T

Best MS KB Ever:

http://support.microsoft.com/kb/555375