StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Ok, as I understand it, if the results already include the pairing (a,b), they shouldn't include (b,a). Is that right? If so, that means that the root cause of your problem is that the tblDynamicUserList table includes both (a,b) and (b,a) pairings. If we filter those in the query first, then the joined results will be what you expected. Note that this also means that you don't care which username is in u_username and which is in f_username; if that mattered, then you would need both in the results.</p> <p>Here is a script demonstrating your current behaviour. Note that the second query in your question didn't produce the results you posted. I have updated it to what I assume you're using:</p> <pre><code>declare @tblUsers table (id int, u_username varchar(20), permission varchar(10), experience int, grade int) declare @tblDynamicUserList table (u_username varchar(20), f_username varchar(20), f_timestamp datetime default (getdate())) insert into @tblUsers values (1, 'RanAbraGmail', 'WiFi', 1, 2000) insert into @tblUsers values (2, 'Ana', 'Phone', 2, 3000) insert into @tblUsers values (3, 'RoyP', 'Phone', 5, 4000) insert into @tblUsers values (4, 'anaHeb', 'Phone', 14, 5000) insert into @tblUsers values (7, 'Sheleg', 'Phone', 15, 5500) insert into @tblDynamicUserList values ('ana', 'RanAbraGmail', default) insert into @tblDynamicUserList values ('anaHeb', 'RoyP', default) insert into @tblDynamicUserList values ('RanAbraGmail', 'Ana', default) insert into @tblDynamicUserList values ('RanAbraGmail', 'RoyP', default) insert into @tblDynamicUserList values ('RoyP', 'anaHeb', default) insert into @tblDynamicUserList values ('Sheleg', 'RanAbraGmail', default) Select D1.u_username, U1.Permission, U1.Grade, D1.f_username, U2.Permission, U2.Grade from @tblDynamicUserList As D1 Join @tblUsers As U1 On U1.u_username = D1.u_username Join @tblUsers As U2 On U2.u_username = D1.f_username </code></pre> <p>Therefore, this query will produce the results you want, by first retrieving only the distinct pairings of usernames, and only then joining to the users table to retrieve the details.</p> <pre><code>Select D1.username1, U1.Permission, U1.Grade, D1.username2, U2.Permission, U2.Grade from (select distinct case when u_username < f_username then u_username else f_username end as username1, case when u_username < f_username then f_username else u_username end as username2 from @tblDynamicUserList) As D1 Join @tblUsers As U1 On U1.u_username = D1.username1 Join @tblUsers As U2 On U2.u_username = D1.username2 </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload