The number of fields in the tables is different. Doubles are possible in the second table (the first goes as a reference).

Table-1 mango fields

  • date
  • from_number

Table-2 email fields

  • dateE,
  • message.

The question is how to remove duplicates in the output (by the fields from_number and message ) and in the output so that you can filter / distinguish (where the data from which table in the field is displayed).

What I want to see is data from the second table without duplicates (taken from the resulting query) or data from the first table without duplicates (taken from the result query) or all together without duplicates. If you just take it through UNION

 SELECT start, from_number FROM mango UNION SELECT DateE, Message FROM email

then a common table with 2 first two fields appears and it is impossible to select values ​​from the second table without duplicates in the output. I tried through LEFT JOIN

 SELECT start, from_number, DateE, Message FROM mango LEFT JOIN email ON mango.from_number != email.Message

but the result is generally delusional 7.5 million code entries in one table 1500 in another 4000 records. It may not be correct, as I think it is forming, and therefore ideas will never come to mind.

    3 answers 3

    what would determine which table you can enter additional field

     select distinct x1,x2,x3 from ( SELECT start as x1 , from_number as x2, 1 as x3 FROM mango UNION SELECT DateE, Message, 2 FROM email ) as t
    • writes: Every derived table must have its own alias. (As far as I understood, sql aliases do not like) - ASYOU
    • one
      after the last bracket add "as t" - minamoto
    • added but no result anyway shows only 2 columns. - ASYOU
    • Added 3 column to query - Xramovnic
    • yes so there was sex - ASYOU

    @Xramovnic In addition to the option other than the one you suggested, I also brought this one: SELECT mango.start, mango.from_number FROM mango LEFT JOIN email ON mango.from_number = email.Message WHERE (mango.finish-mango.start>59) AND mango.from_number >0 AND email.Message IS NULL AND I SELECT mango.start, mango.from_number FROM mango LEFT JOIN email ON mango.from_number = email.Message WHERE (mango.finish-mango.start>59) AND mango.from_number >0 AND email.Message IS NULL AND all calls for more than a minute, remove zero phones and after joining remove those values ​​that match. Thanks to all.

       SELECT DISTINCT start , from_number -- 'mango' as tablename FROM mango UNION ALL SELECT DISTINCT DateE , Message -- 'email' FROM email

      UPD:

       SELECT start , from_number -- 'mango' as tablename FROM mango UNION ALL SELECT email.DateE , email.Message -- 'email' FROM email LEFT JOIN mango ON email.DateE = mango.start AND email.Message = mango.from_number WHERE mango.start IS NULL
      • The distinction does not fit here (as far as I remember removes duplicates) because It is necessary to remove duplicates in the second table as compared with the first one and only then to add it so that we can say so the summary data. And you first remove duplicates in the tables themselves and then it all comes down to one. - ASYOU
      • Well, I would have written that you need all of the first plus of the second, which is not in the first. Code added. - Akina