CODEWITHSUNDEEP
mysqldatabase
I'll-Be-Back
I'll-Be-Back

Reputation: 10828

Show all the fields from JOIN table in the UNION?

How to show some fields from tariff_data table that is already include in the UNION with JOIN?

For example:

SELECT  X.* FROM
    (
         SELECT phone_id, tariff_id, active  FROM table1 WHERE id = 5
      UNION
          SELECT dgt.phone_id, tns.tariff_id, dgt.active  FROM  table2 AS dgt 
                JOIN tariff_data AS tns ON tns.something = dgt.something
          WHERE id = 5
    ) as X

Upvotes: 0

Views: 53

Answers (2)

ron tornambe
ron tornambe

Reputation: 10780

You will need to include the field from tariff_data in both Select statements, setting the first to null or whatever you like:

SELECT  X.* FROM
(
    SELECT phone_id, tariff_id, active, null as "somefield"  FROM table1 WHERE id = 5
      UNION
    SELECT dgt.phone_id, tns.tariff_id, dgt.active, tns.somefield FROM  table2 AS dgt 
            JOIN tariff_data AS tns ON tns.something = dgt.something
    WHERE id = 5
) as X

Upvotes: 1

ron tornambe
ron tornambe

Reputation: 10780

SELECT  X.* FROM
(
    SELECT phone_id, tariff_id, active, null as "somefield"  FROM table1 WHERE id = 5
      UNION
    SELECT dgt.phone_id, tns.tariff_id, dgt.active, tns.somefield FROM  table2 AS dgt 
            JOIN tariff_data AS tns ON tns.something = dgt.something
    WHERE id = 5
) as X

Upvotes: 1

Related Questions