Search frequency in Mysql tables

Asked

Viewed 129 times

1

This is my question. The system (PHP) has 6 tables (Mysql) identical which are user participation in certain events. Each entry in the table is unique but the data can be repeated in the tables. What I’m not able to do is search these 6 tables which users participated in all 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
  • 1

    If the tables are identical, why don’t you normalize them? Make all data stay in the same table and differentiate through a new column.

  • And there’s no way we can answer without seeing the structure of the tables.

  • 1

    Post the table structure, but from what you said it seems to use JOIN between tables resolves.

  • I did not understand the idea of taking the table. It would help in the answers.

2 answers

2

Without seeing the tables is kind of 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)

1

A 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

  • Thank you very much for the reply. Apart from the TABLE of GROUP BY worked perfectly.

  • Here it worked like this. However I tested in SQL-Server and not in MYSQL.

Browser other questions tagged php mysql sql

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