>I need a special INNER JOIN which I cannot find a way to do. Basically, I have a table having NoMember and NoMember2 fields. So, if I would look into my profile, I could see records where NoMember is to my ID or some records that NoMember2 is also to my ID. From that, I need to create a list where I would show the other Member.FirstName field.
>
>If I would do somelike like this, I would only get the records where NoMember is to my ID:
>
>
>SELECT Member.FirstName FROM Friend
> WHERE Friend.NoMember=1 OR Friend.NoMember2=1
> INNER JOIN Member ON Friend.NoMember=1
>
>
>To achieve a combination of both fields, I would need something similar to this:
>
>
>SELECT Member.FirstName FROM Friend
> WHERE Friend.NoMember=1 OR Friend.NoMember2=1
> INNER JOIN Member ON (Friend.NoMember=1 OR Friend.NoMember2=1)
>
>
>However, this would create a duplicate of all records I would get in the first SQL. What I need is to obtain the same amount of records as the first SQL but to show the relation on the other field which ever is not having NoMember=1 or NoMember2=1.
>
>Is this possible?
I guess Sergey already answered it, but I would answer anyway, since this is the question I answered just a day ago on another forum too (I haven't yet checked other answers in this thread). You need to use UNION here, e.g.
select Member.FirstName from Friend INNER JOIN Member ON ?
UNION ;
...
See
http://forums.asp.net/t/1396533.aspx
If it's not broken, fix it until it is.
My Blog