Mysql Question posted in
The official MySQL documentation can be found here.

Fetch matched and unmatched records and different set of columns from two tables in mysql query

I have two tables having different columns and two columns are common.
Some of records are same in both the tables. Need to query to fetch all two table records with joining column,
Used MySQL database.

Table1 – T1

transaction_id, device_id, is_valid, description
123             DEV001     1         valid device
123             DEV002     0         Device doesn't exist

Table – T2

 transaction_id, device_id, error,      status, store_id
 123             DEV001     valid device Success     12

The query should return below records,

123             DEV001     1         valid device          Success    12
123             DEV002     0         Device doesn't exist  Failed
123             DEV001     1         valid device          Success     12

The first table holds both valid and invalid devices based on column is_valid but second table contains only valid devices with no column is_valid.

So, I need all records from second table and first table need only is_valid false records based on filter column transaction_id.

Tags:

2

Answers


  1. 0

    It seems you need following query. Since we’re always fetching invalid item from t1, we can hardcode status column as Failed & since t2 always contains valid item, we can hardcode is_valid as 1.

    select transaction_id, device_id, is_valid, description, 'Failed' status
from t1 where is_valid = 0

union

select transaction_id, device_id, 1 is_valid, error as description, status
from t2
    Login or Signup to reply.
  2. 0

    UNION ALL the tables. Use window functions to copy T2 values to corresponding T1 rows where absent.

    select transaction_id, device_id, is_valid, description as valid_device,
       coalesce(max(status) over (partition by transaction_id, is_valid),
                'Failed') as status,
       max(store_id) over (partition by transaction_id, is_valid) as store_id
from
(
    select transaction_id, device_id, is_valid, description, null as status, null as store_id
    from T1
    union all
    select transaction_id, device_id, 1, error, status, store_id
    from t2
) dt
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search