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

Mysql view from tables without duplicates and a concatenated column

I need to collect data from different tables, without duplicates and with a column that collects the presence where I’ve found each item. So, this is my scenario:

TABLEA

itemId name
1 John
2 Sam

TABLEB

itemId name
1 John
3 Thomas

I need a view with this result:

itemId source_tables
1 TABLEA,TABLEB
2 TABLEA
3 TABLEB

TRY 1 with this simple UNION query

SELECT TABLEA.itemId AS itemId, ‘TABLEA’ AS source_tables FROM TABLEA

UNION

SELECT TABLEB.itemId AS itemId, ‘TABLEB’ AS source_tables FROM TABLEB

I get this result:

itemId source_tables
1 TABLEA
2 TABLEA
1 TABLEB
3 TABLEB

but itemId obviously are duplicated.

TRY 2 with this query

SELECT itemId, group_concat(source_table separator ‘;’) AS source_tables FROM
(
SELECT itemId, ‘TABLEA’ AS source_table FROM TABLEA

UNION ALL

SELECT itemId, ‘TABLEB’ AS source_table FROM TABLEB
) T1
GROUP BY itemId

I get the expected result. Does anybody knows a better way? is it the best approach?

Tags:

2

Answers


  1. 0

    If you have only two tables, TABLEA and TABLEB, and there are no duplicate item IDs within each table, the following query is expected to be more efficient and performant.

    select coalesce(a.itemid,b.itemid) as itemid , case when a.itemid=b.itemid then 'TABLEA' ||','|| 'TABLEB' WHEN a.itemid is null then 'TABLEB' else 'TABLEA'    end as source_tables
from TABLEA a full outer join TABLEB b on a.itemId =b.itemId
order by  a.itemId asc

    you can define index on itemID to improve the prformance

    Login or Signup to reply.
  2. 0

    Try this:

    select a.itemid, case when b.name is null then 'tablea' else 'tablea,tableb' end as source_tables
from tablea a
left join tableb b
using(itemid,name)
union
select b.itemid, case when a.name is null then 'tableb' else 'tablea,tableb' end as source_tables
from tablea a
right join tableb b
using(itemid,name)
;

-- result set:
# itemid    source_tables
1   tablea,tableb
2   tablea
3   tableb

    Note: Since the ORDER BY clause does not work in UNION, if sorting is required, make the UNION statement a subquery(derived table) so we can use the ORDER BY clause on it then.

    select * 
from (
    select a.itemid, case when b.name is null then 'tablea' else 'tablea,tableb' end as source_tables
    from tablea a
    left join tableb b
    using(itemid,name)
    union
    select b.itemid, case when a.name is null then 'tableb' else 'tablea,tableb' end as source_tables
    from tablea a
    right join tableb b
    using(itemid,name)
    ) t
order by itemid desc -- here we sort in descending order
;

--result set:
# itemid    source_tables
3   tableb
2   tablea
1   tablea,tableb
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search