Mysql view from tables without duplicates and a concatenated column

Question

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?

blabla_bingo · Accepted Answer · 2023-07-03 07:21:24Z

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

Collectives™ on Stack Overflow

Mysql view from tables without duplicates and a concatenated column

TRY 1 with this simple UNION query

TRY 2 with this query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

TRY 1 with this simple UNION query

TRY 2 with this query

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related