Query 2 tables in a sql database and display repeated values [closed]

Navigation
0

I have 2 tables in my database, one call pessoas and another call resultado , I am filling the result table with the draws made by mega sena, the result table has the fields: 

data, dez1, dez2, dez3, dez4, dez5, dez6

The people table already has the fields: 

nome, dez1, dez1, dez3, dez4, dez5, dez6

I would like to make a select where it shows me exactly which balls people have hit, in case they show the tens that are the same in both tables, they do not have a foreign key.

    
asked by anonymous 10.10.2017 / 05:23

1 answer

1

One way to do it may not be the best but it is possible to do the following: 

SELECT 'pessoas'.'name', 'pessoas'.'dez1' FROM resultado
INNER JOIN pessoas ON 'resultado'.'dez1'='pessoas'.'dez1'

This returns all records in the pessoas table with values equal to dez1 of the resultado table. To obtain the remaining results simply change the value 1 by 2, 3, 4, 5 and 6.

  

Real example in SQL Fiddle .

EDIT1

I was able to improve the query, for example: 

SELECT pessoas.name, pessoas.dez1 FROM pessoas
WHERE dez1 IN (SELECT dez1 FROM resultado WHERE data=140317)
OR dez1 IN (SELECT dez2 FROM resultado WHERE data=140317)
OR dez1 IN (SELECT dez3 FROM resultado WHERE data=140317)
OR dez1 IN (SELECT dez4 FROM resultado WHERE data=140317)
OR dez1 IN (SELECT dez5 FROM resultado WHERE data=140317)
OR dez1 IN (SELECT dez6 FROM resultado WHERE data=140317);

In this query we make a select to the first value of all the people, and then we will compare if it is equal to any of the 6 of the result. It also includes the condition to know how to go looking for the right day.

  

Real example in SQL Fiddle .

    
10.10.2017 / 10:56
Monitor WebView url Majority Image Algorithm Analysis