getting two distinct fields from one tbl from a join query of two tables

Hi

Help! Im stuck on a join query.

Im trying to get distinct (the same row returned only once) user_id and
forename from a tbl of users (they are always distinct) where user_id
in that tbl equals friend_id in another table friend_id, which 'should'
be unique ie there 'should' only be one row of a 'friend connection',
but in the case of bugs etc this might not be true, what i want to do
is select only the row of tblusers with the user details that are
matched by join condition below, but only ONCE for each, ie not in as
many times as there are the same connection in tblfriends.

For some reason the result i am getting is a repitition of the 2 fields
in tblusers (that i am selecting) for as many rows as there are in
tblfriends that match the join condition.

How can i make it only return a row once?

SELECT tblusers.user_id, tblusers.forename FROM tblusers LEFT JOIN
tblfriends ON tblusers.user_id = tblfriends.friend_id WHERE
tblfriends.user_id = '290'

Please help!

Thanks

Marc

Re: getting two distinct fields from one tbl from a join query of two tables

On Nov 15, 9:28 am, "mcyi2...@googlemail.com"
wrote:
> Hi
>
> Help! Im stuck on a join query.
>
> Im trying to get distinct (the same row returned only once) user_id and
> forename from a tbl of users (they are always distinct) where user_id
> in that tbl equals friend_id in another table friend_id, which 'should'
> be unique ie there 'should' only be one row of a 'friend connection',
> but in the case of bugs etc this might not be true, what i want to do
> is select only the row of tblusers with the user details that are
> matched by join condition below, but only ONCE for each, ie not in as
> many times as there are the same connection in tblfriends.
>
> For some reason the result i am getting is a repitition of the 2 fields
> in tblusers (that i am selecting) for as many rows as there are in
> tblfriends that match the join condition.
>
> How can i make it only return a row once?
>
> SELECT tblusers.user_id, tblusers.forename FROM tblusers LEFT JOIN
> tblfriends ON tblusers.user_id = tblfriends.friend_id WHERE
> tblfriends.user_id = '290'
>
> Please help!
>
> Thanks
>
> Marc

How about:

SELECT DISTINCT tblusers.user_id, tblusers.forename
FROM tblusers
LEFT JOIN tblfriends ON tblusers.user_id = tblfriends.friend_id
WHERE tblusers.user_id = '290'

?