Query between tables

Asked

Viewed 66 times

1

I need to make a query as follows: select a jovem that is in the same cidade and estado that the mentor and that has the largest number of equal features that of the mentor.

My consultation:

SELECT * 
FROM jovem 
INNER JOIN mentor ON jovem.jcidade = mentor.mcidade AND jovem.jestado = mentor.mestado 
WHERE jovem.jfaixaetaria = mentor.mfaixaetaria
  AND jovem.jescolaridade = mentor.mescolaridade 
  AND jovem.jhobby = mentor.mhobby 
  AND jovem.jcomida = mentor.mcomida 
  AND jovem.jmusica = mentor.mmusica 
  AND jovem.jesporte = mentor.mesporte 
  AND jovem.jtime = mentor.mtime 
  AND jovem.jcaracteristica = mentor.mcaracteristica 
  AND jovem.janimal = mentor.manimal 
  AND jovem.jlivro = mentor.mlivro 
  AND jovem.jsonho = mentor.msonho

Table structure: inserir a descrição da imagem aqui

mysql select

  • Have you tried something? put your code to try to help. (https://answall.com/help/how-to-ask)

  • I need to add the COUNT in the fields, the 2 that have more equal combinations will be selected.

  • blz, now what’s the problem with this query? error? returns too much? returns nothing?

  • He makes no mistake... returns the "young man" who has the answers "equal" to the mentor’s... but I need him to make a "COUNT" from: the young 1 had 4 equal answers from some tutor - the young 2 had 5 equal answers to the tutor...

  • he returns the jovem the same because your query only brings if all characteristics are equal. but you want the one that has more characteristics in common, is that it? In the case of the 11 possible (not counting city and state) you want to know who has more (type jovem_1 has 6 equal with monitor_1, jovem_2 has 3 equal with monitor_2).. that’s it?

  • that’s right! I would only change to: jovem_1 has 6 equal with monitor_1, jovem_2 has 3 equal with monitor_1)

  • What are these features? the columns of each table(all) or have some that should not be compared as email and name?

  • Name, email, photo do not need to be compared

Show 3 more comments

2 answers

2


One way you can do to achieve the result is to compare the columns and with CASE WHEN returning 1 for equal values and 0 for different, in the end just add the return of each comparison, follows example below.

Note: I put only 2 comparisons because I don’t know what are the characteristics (columns) you want to count.

SELECT 
  jnome AS jovem
  , mnome AS Mentor
  , (
    (CASE WHEN jfaixaetaria = mfaixaetaria THEN 1 ELSE 0 END) +
    (CASE WHEN jescolaridade = mescolaridade THEN 1 ELSE 0 END)
  ) AS qtdCaracteristicasIguais
FROM ligacao
LEFT JOIN mentor ON ligacao.mentor = mentor.CPF
LEFT JOIN jovem ON ligacao.jovem = jovem.CPF
WHERE mcidade = jcidade
AND mestado   = jestado

Follow example working online:

Sqlfiddle

  • Hello, Caique! Would you be able to do this code without linking with the link table? My link table has nothing yet...

  • Caique, I did the tests here and it worked! But I have 9 "variables" equal, but it appears 17 in the qtyCaracterShows...you know what might be happening?

  • Sorry it took so long to answer. In order to help you, the ideal would be to open a new question. Include in it the query you ran, the tables involved, the result obtained by the query and the expected result. With this information you can simulate your environment.

  • 1

    Thank you, Caique!

1

untested code

I couldn’t test it here, but here’s the idea:

  1. create a temporary table to store what matches each jovem and monitor who live in the same city;
  2. in the query, add the total of "coincidences"

Try to do some tests, in case the logic isn’t right, we try to tidy up.

CREATE TEMPORARY TABLE IF NOT EXISTS tabelaTemporaria AS (
    SELECT jovem.jid as 'jid', 
        mentor.mid as 'mid', 
        CASE jovem.jfaixaetaria WHEN mentor.mfaixaetaria THEN 1 else 0 end as 'faixaetaria',
        CASE jovem.jescolaridade WHEN mentor.mescolaridade THEN 1 else 0 end as 'escolaridade',
        CASE jovem.jhobby WHEN mentor.mhobby THEN 1 else 0 end as 'hobby',
        CASE jovem.jcomida WHEN mentor.mcomida THEN 1 else 0 end as 'comida',
        CASE jovem.jmusica WHEN mentor.mmusica THEN 1 else 0 end as 'musica',
        CASE jovem.jesporte WHEN mentor.mesporte THEN 1 else 0 end as 'esporte',
        CASE jovem.jtime WHEN mentor.mtime THEN 1 else 0 end as 'time',
        CASE jovem.jcaracteristica WHEN mentor.mcaracteristica THEN 1 else 0 end as 'caracteristica',
        CASE jovem.janimal WHEN mentor.manimal THEN 1 else 0 end as 'animal',
        CASE jovem.jlivro WHEN mentor.mlivro THEN 1 else 0 end as 'livro',
        CASE jovem.jsonho WHEN mentor.msonho THEN 1 else 0 end as 'sonho'
    FROM jovem INNER JOIN mentor ON jovem.jcidade = mentor.mcidade AND jovem.jestado = mentor.mestado 
)

SELECT jid, mid, (faixaetaria + escolaridade + hobby + comida + musica + esporte + time + caracteristica + animal + livro + sonho) as total_caracteristicas
FROM tabelaTemporaria
ORDER BY total_caracteristicas DESC
--LIMIT 2 --LIMIT é opcional para o total que precisar de retornos

Browser other questions tagged mysql select

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