1

I've been having some trouble with an SQL statement I'm trying to create and I figured that someone here would probably be able to help me out.

I've got two tables: Matches and Users. The Matches table consists of User_id and Partner_id that represents a user and their respective match (partner). Each time a user clicks a button, a new match will be found and a row will be created in the Matches table.

Now, when a user clicks the button to find a match, I need to - randomly - find a user in the Users table that is not the user himself (obviously) and does not already have a relation to the current user in the Matches table. - This is where I get stuck.

How can I pick a user based on the criteria that they do not already have a relation to the currently logged in user, through the Matches table?

All answers are much appreciated. Thank you.

Edit: Having read both SQL - find records from one table which don't exist in another and Sql select row if no row exists in other table (as well as others) only had me more confused afterwards. - I simply didn't quite understand the answers that were given well enough to convert them to what would have been useful in my case.

Community
  • 1
  • 1
  • Your question is not very clear... No code either.. Your context is not only SQL, but application + db. What do you mean by " a user that has not already been matched with the user." ? – Rémi Becheras Jul 12 '15 at 11:08
  • 1
    @RémiBecheras I apologize that you feel I was not clear enough. I was trying not to complicate something otherwise quite simple, as to avoid any misunderstandings regarding what I was attempting. - I will keep the simplicity of the question, but I will edit it to include a better explanation of the line you are questioning. - What do you mean by this being application + DB, though? Though I do plan to use it in my application, this is only a question about the SQL. - Mind explaining what you meant? –  Jul 12 '15 at 11:41
  • @kordirko I partially agree that it's a duplicate. I had already read the one you linked, as well as several others, but did not feel that I had gotten my answer. - Probably because I'm still quite new to this and I was unable to alter the given answers in the other threads, to solve the problem I was facing. Thank you for notifying me about it, though. –  Jul 12 '15 at 11:42

1 Answers1

1
select
    user_id
from
    users u
where
    user_id<>$own_id
    and not exists (
      select * from matches m
      where
          m.user_id=u.user_id
          or m.partner_id=u.user_id
    )
order by rand()
limit 1

Although note that order by rand() is not the most efficient solution. Read more here and consider some alternatives.

Community
  • 1
  • 1
Vilx-
  • 104,512
  • 87
  • 279
  • 422