CODEWITHSUNDEEP
mysqlsql
Anonymous
Anonymous

Reputation: 1094

How to combine same table using union in mysql

I want to get mysql records using union by combining same table based on different id 

Table name :sample_value

id   width  userid
1    10X12   1
2    14X15   1
3    17X28   2
4    10X12   2
5    14X15   2
6    19X37   2

Mysql Union query: 

select * from table sample_values where userid=1 
union distinct
select * from table sample_values where userid=2

Suppose I want to get records based on two different id using union lets say:

1) if userid=1 means it should get 10X12 and 14X15
2) if userid =2 means it should get 17X28 and combine with previous record of userid=1 but record 10X12 and 14X15 not get from userid=2 because same value is already present

so I wrote query for this union query for this but it didn't help me Expected output:

id   width  userid
1    10X12   1
2    14X15   1
3    17X28   2
6    19X37   2

Upvotes: 1

Views: 667

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269543

This is a prioritization query. Others have already shown how to use union all/not exists for this. That doesn't generalize well when you have more than two or three values:

A more general solution is to use window functions, which are in MySQL 8+:

select sv.*
from (select sv.*,
             row_number() over (partition by width
                                order by (case when user_id = 1 then 1 else 2 end)
                               ) as seqnum
      from sample_value sv
     ) sv
where seqnum = 1;

You can also do this without a union all:

select sv.*
from sample_value sv
where sv.user_id = (select sv2.user_id
                    from sample_value sv2
                    where sv2.width = sv.width
                    order by (case when sv2.user_id = 1 then 1 else 2 end)
                    limit 1
                   );

In all these example, I have used a case for the order by. For your particular examples, order by user_id is sufficient.

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133360

You could try using the union between the userid=1 and the not matching value between userid =1 and userid =2

    select * 
    from table sample_values 
    where userid=1 
    union (
    select b.* 
    from table sample_values a
    left JOIN sample_values b on a.width = b.with 
        and a.userid = 1 and b.userid=2
    where b.width is null
    ) t

OR

Instead of an union You could try using a join with agregated 

    select s.* from sample_values s
    inner join (
        select width, min(userid) min_width
        from sample_values 
        group by width
    ) t on t.min_width= s.width and t.userid = s.userid

Upvotes: 0

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28834

Instead of UNION, you can utilize GROUP BY as well:

SELECT
  width, 
  MIN(userid) AS userid
FROM sample_values 
WHERE userid IN (1,2)
GROUP BY width

MIN(userid) will ensure that you give preference to earliest userid. This query would also be more efficient than using UNION with Subqueries.

Result View on DB Fiddle

| width | userid |
| ----- | ------ |
| 10X12 | 1      |
| 14X15 | 1      |
| 17X28 | 2      |
| 19X37 | 2      |

Upvotes: 0

forpas
forpas

Reputation: 164069

Use NOT EXISTS in the 2nd query of the UNION:

select * from sample_values where userid=1 
union all
select * from sample_values s
where s.userid = 2 and not exists (
  select 1 from sample_values
  where userid = 1 and width = s.width 
)

See the demo.
Results:

| id  | width | userid |
| --- | ----- | ------ |
| 1   | 10X12 | 1      |
| 2   | 14X15 | 1      |
| 3   | 17X28 | 2      |
| 6   | 19X37 | 2      |

Upvotes: 1

Related Questions