MYSQL SELECT possible value of two tables

Question

MYSQL SELECT possible value of two tables

Navigation

#1 by (1 votes)

0

I have 2 tables, one called 'families' with fields 'id', 'email', and another one called 'progenitor' with fields 'id', 'family_id', 'email' (simplifying the rest of the fields).

I need to make a SELECT in which to return the email (if any) from the "families" table and each email from the "parent" table of that family, in case it is different from the family.

I have something like:

SELECT progenitor.email AS correo_progenitor,
       familias.email AS correo_familia,
       FROM familias
       LEFT INNER JOIN progenitor
       ON progenitor.id_familia = familias.id
       WHERE progenitor.email IS NOT NULL
       AND familias.email <> ''
       AND progenitor.email <> familias.email

What I would need is a list of the non-repeated emails from both the families and parent table. With the code above only those in the parent table are shown. I do not know if it would be a matter of putting another SELECT inside but maybe there is a more effective formula of this type.

Thanks

mysql join select

asked by David Bravo 13.10.2017 в 21:16

source

1 answer

Pass elements from a Listbox to a two-dimensional array in C # ruby on rails current.user devise

score 1 · Accepted Answer

The filter by different emails you have to do at the level of JOIN if you do it at the level of WHERE you will not show the rows whose email is the same

SELECT  familias.id,
    progenitor.email    AS mail,
    familias.email      AS correo_familia
    FROM familias
    LEFT JOIN progenitor
        ON progenitor.id_familia = familias.id
        and IFNULL(progenitor.email, '') <> IFNULL(familias.email, '')
    WHERE   IFNULL(familias.email,'') <> ''

On the other hand, keep in mind that by doing this: IFNULL(familias.email,'') <> '' , you can not recover the cases in which you do not have mail in familias but in progenitor . The use of the IFNULL is simply because I am not clear about the behavior of the comparisons with NULL in MySql.