Dear All,

I just can't seem to get myself a nice query.
I have a table with data about people meeting eachother. They meet
randomly and now I want to show a history, about who you met and how
many times.

Table and contents could be as followed:

userleft_id | userright_id

1 2
2 4
5 2
6 1
4 2

As you can see, users 4 and 2 met eachother twice, the rest only once.
So if I want to show user 2 who he met results should be:

User 2 met:

1 1 time
4 2 times
5 1 time


How can I get this result from here? The table as thousands of rows.
Of course, I could retrieve everything in PHP and compare everything
but I guess it could be done in SQL as well.

In another table the names of all these people can be found which I
want to have as well in a JOIN.
Normally, I would use

INNER JOIN usernames ON meetings.userleft_id=usernames.user_id

but this time I do not know whether it is userleft_id or userright_id
of who I want to have the name.


Anyone have a suggestion?


Kind regards,


Pim Zeekoers

On 19 Oct, 16:03, p...@impulzief.nl wrote:
> Dear All,
>
> I just can't seem to get myself a nice query.
> I have a table with data about people meeting eachother. They meet
> randomly and now I want to show a history, about who you met and how
> many times.
>
> Table and contents could be as followed:
>
> userleft_id | userright_id
>
> 1 2
> 2 4
> 5 2
> 6 1
> 4 2
>
> As you can see, users 4 and 2 met eachother twice, the rest only once.
> So if I want to show user 2 who he met results should be:
>
> User 2 met:
>
> 1 1 time
> 4 2 times
> 5 1 time
>
> How can I get this result from here? The table as thousands of rows.
> Of course, I could retrieve everything in PHP and compare everything
> but I guess it could be done in SQL as well.
>
> In another table the names of all these people can be found which I
> want to have as well in a JOIN.
> Normally, I would use
>
> INNER JOIN usernames ON meetings.userleft_id=usernames.user_id
>
> but this time I do not know whether it is userleft_id or userright_id
> of who I want to have the name.
>
> Anyone have a suggestion?
>
> Kind regards,
>
> Pim Zeekoers

UNION 2 queries, one based on your person being in userleft and the
other based on them being in userright, then sum the results in a
query containing the union.