Query returning users who have a set of records from another table

Asked

Viewed 30 times

2

I developed a small system in PHP and Mysql that aims to store and manage users and their respective courses.

I searched some forums and pages in Google, but I did not find any information to help me in the resolution of this query.

I’m struggling to assemble a query that returns all users who have a specific set of courses. The courses are recorded in a separate table.

For ease of understanding, below is a summary diagram of the table:

Summary scheme:

  Tabela Usuarios
    usu_codigo - ID do usuário
    usu_nome   - Nome do Usuário

  Tabela Cursos
    cur_usuario - ID do usuário (chave estrangeira)
    cur_codigo  - ID do curso

Example:

Bruno - has the courses 3,5,7,9,10,12
John - has the courses 3,7,8,9,10,15
Maria - has the courses 2,5,7,10,11,13

When requesting users who have the courses 5,7,10, should return, therefore, Bruno and Maria. John should not be included, because he does not have the course 5.

That is, the objective is not to return users who have at least one of the requested courses, but all of them at the same time.

I set up the summary structure of Mysql in SQL Fiddle:
http://sqlfiddle.com/#! 9/29a204/8

I appreciate anyone who can instruct me on this.

mysql sql

1 answer

2


To expected is the following make a grouping in the table of cursos and according to the number of parameters that in the case are 3 (5, 7 e 10) check which of these returns is equal to 3 and unite the result with the table of usuarios with JOIN:

SELECT usuarios.* FROM usuarios 
INNER JOIN (
SELECT cur_usuario, 
  COUNT(cur_usuario) AS Q FROM cursos
  WHERE cur_codigo IN (5,7,10)
  GROUP BY cur_usuario
  HAVING COUNT(cur_usuario) = 3)
AS S ON S.cur_usuario = usuarios.usu_codigo

Upshot ONLINE.

  • Hello, Novic! Thank you very much for your help. It helped me a lot =D. So it is necessary to make use of a second select to get the expected result, isn’t it? A hug!

  • @Luis yes it is necessary to use a preparation of results to then search in the other table, if it is useful for you vote as solution of your problem.

  • I have a new question. If it is not necessary q users have tds of the mentioned courses and yes at least 1. I then set up the query for this, which works perfectly. select * from usuarios USU INNER JOIN courses CUR ON USU.usu_codigo=CUR.cur_usuario Where CUR.cur_codigo in (5,7,10) group by USU.usu_codigo order by USU.usu_nome asc Will the query still travel to see if it also has courses 7 and 10? If there really are these unnecessary checks, is there a way to optimize it? http://sqlfiddle.com/#! 9/29a204/51

  • Yes he go line by line in search of the proposed comparison, but, I would use so: select distinct USU.* from usuarios USU INNER JOIN cursos CUR ON USU.usu_codigo=CUR.cur_usuario where CUR.cur_codigo in (5,7,10) order by USU.usu_nome asc @Luis (If you have more questions you can open another question)

  • The DISTINCT causes some record not to be repeated and in case the GROUP BY and unnecessary because you don’t need to tell or summarize anything ... @Luis

  • let’s delete this lot of comments @Luis?

Show 1 more comment

Browser other questions tagged mysql sql

You are not signed in. Login or sign up in order to post.