sql record in tables and return the table name

Question

Well, here’s the thing query Mysql;

    SELECT idMembro
    FROM membros 
    WHERE
       idMembro IN (
                  SELECT idMembro FROM pastores
            union SELECT idLider  FROM redes
            union SELECT idLider  FROM regioes
            union SELECT idLider  FROM areas
            union SELECT idLider  FROM setores
            union SELECT idLider  FROM celulas
       ) 
    ORDER BY nome

My goal is to know if the idMembro that I seek is present in one of these tables and, if it is, return the name of the table that was found the idMembro.

If you don’t find it, come back null.

As a rule, the same idMembro cannot be in 2 tables at the same time.

How to do this?

Answer 1

See this querie using sub query

 select t.idMembro, t.nomeTabela from (

          SELECT idMembro as idMembro, 'pastores' as  nomeTabela FROM pastores
    union SELECT idLider as idMembro, 'redes'   as  nomeTabela FROM redes
    union SELECT idLider as idMembro, 'regioes' as  nomeTabela FROM regioes
    union SELECT idLider as idMembro, 'areas'   as  nomeTabela FROM areas
    union SELECT idLider as idMembro, 'setores' as  nomeTabela FROM setores
    union SELECT idLider as idMembro, 'celulas' as  nomeTabela FROM celulas
) t
where t.idMembro = :idMebroConsultado

Replace :idMebroSet with the id you want, or remove Where if you want to see all.

See the darling here http://sqlfiddle.com/#! 9/5da2e0/2

Answer 2

Try it this way (there are others):

SELECT      ME.*
        ,   CASE WHEN PA.idMembro IS NOT NULL 
            THEN 'pastores'
            ELSE
            (   CASE WHEN RD.idLider IS NOT NULL 
                THEN 'redes'
                ELSE
                (   CASE WHEN RE.idLider IS NOT NULL 
                    THEN 'regioes'
                    ELSE
                    (   CASE WHEN AR.idLider IS NOT NULL 
                        THEN 'areas'
                        ELSE
                        (   CASE WHEN SE.idLider IS NOT NULL 
                            THEN 'setores'
                            ELSE
                            (   CASE WHEN CE.idLider IS NOT NULL 
                                THEN 'celulas'
                                END
                            )
                            END
                        )
                        END
                    )
                    END
                )
                END
            )
            END AS Tabela
FROM        membros     ME
LEFT JOIN   pastores    PA ON PA.idMembro = ME.idMembro
LEFT JOIN   redes       RD ON RD.idLider  = ME.idMembro
LEFT JOIN   regioes     RE ON RE.idLider  = ME.idMembro
LEFT JOIN   areas       AR ON AR.idLider  = ME.idMembro
LEFT JOIN   setores     SE ON SE.idLider  = ME.idMembro
LEFT JOIN   celulas     CE ON CE.idLider  = ME.idMembro
WHERE       PA.idMembro   IS NOT NULL
        OR  RD.idLider    IS NOT NULL
        OR  RE.idLider    IS NOT NULL
        OR  AR.idLider    IS NOT NULL
        OR  SE.idLider    IS NOT NULL
        OR  CE.idLider    IS NOT NULL
ORDER BY    ME.nome

---

Optimizing (and completing) the query @Martinomadic, where the link to the table was missing membros:

SELECT      ME.*
        ,   IFNULL(TMP.nomeTabela, '') AS Tabela
FROM        membros     ME
LEFT JOIN   (
                SELECT idMembro AS idMembro, 'pastores' AS  nomeTabela FROM pastores
                UNION 
                SELECT idLider  AS idMembro, 'redes'    AS  nomeTabela FROM redes
                UNION 
                SELECT idLider  AS idMembro, 'regioes'  AS  nomeTabela FROM regioes
                UNION 
                SELECT idLider  AS idMembro, 'areas'    AS  nomeTabela FROM areas
                UNION 
                SELECT idLider  AS idMembro, 'setores'  AS  nomeTabela FROM setores
                UNION
                SELECT idLider  AS idMembro, 'celulas'  AS  nomeTabela FROM celulas
            )           TMP ON TMP.idMembro = ME.idMembro