Find frequency in MySQL tables

Question

Find frequency in MySQL tables

Navigation

#1 by (2 votes)
#2 by (1 votes)

1

Here is my doubt. The system (PHP) has 6 identical tables (MySQL) that are the participations of users in certain events. Each entry in the table is unique but the data can be repeated in the tables. What I am not able to do is to search in those 6 tables which users participated in all the events.

+-------------------+---------+------+-----+---------+----------------+
| Field             | Type    | Null | Key | Default | Extra          |
+---------------------------------------------------------------------+
| id                | int(11) | NO   | PRI | NULL    | auto_increment |
| idPiloto          | int(11) | NO   |     | NULL    |                |
| pontosGanhos      | int(11) | NO   |     | NULL    |                |
| pontosDeduzidos   | int(11) | NO   |     | NULL    |                |
| carteiraGanhos    | int(11) | NO   |     | NULL    |                |
| carteiraDeduzidos | int(11) | NO   |     | NULL    |                |
| dnf               | int(11) | NO   |     | NULL    |                |
| dq                | int(11) | NO   |     | NULL    |                |
| dqRe^             | int(11) | NO   |     | NULL    |                |
| bateriaGrid       | int(11) | NO   |     | NULL    |                |
| posicao           | int(11) | NO   |     | NULL    |                |
| posicaoRe^        | int(11) | NO   |     | NULL    |                |
+-------------------+---------+------+-----+---------+----------------+

php mysql sql

asked by anonymous 24.11.2016 / 15:47

2 answers

Two effects with javascript in one image Remove border that separates columns from a table

score 2 · Answer 1

Without looking at the tables it is a bit difficult to give you a solution, but the query would look something like this:

SELECT u.*
  FROM usuarios u
 WHERE EXISTS(SELECT x.*
                FROM (SELECT * FROM tabela1 AS t1
                      UNION
                      SELECT * FROM tabela2 AS t2
                      ...
                      UNION
                      SELECT * FROM tabela6 AS t6) x
               WHERE x.usuario_id = u.usuario_id)

score 1 · Answer 2

One solution.

SELECT COUNT(*), TABELA, idPiloto FROM (
    SELECT 'TAB1' AS TABELA, idPiloto FROM TABELA1
    UNION 
    SELECT 'TAB2' AS TABELA, idPiloto FROM TABELA2
    UNION   
    .....
    SELECT 'TAB6' AS TABELA, idPiloto FROM TABELA6  ) A
GROUP BY TABELA, idPiloto
HAVING COUNT(*) = 6