6

I have a problem with the correct syntax to use UNION and GROUP_CONCAT in this situation:

I have 4 tables:

  • base: Is the main table with a lot of columns.
  • mm: Is a mm table that points to the next two tables using a 'tablenames' field.
  • t1 and t2 that stores data related.

Records in 'base' tables can have many related records in t1 and t2 through the mm table.

I'm creating a VIEW in MySQL and I need all those related records are displayed in a single column separated by commas.

This is the base MySQL code: 

SELECT base.uid, t1.nombre_es
FROM base
INNER JOIN mm 
ON mm.uid_local=base.uid
INNER JOIN t1 
ON mm.uid_foreign=t1.uid WHERE mm.tablenames = 't1'

UNION

SELECT base.uid, t2.nombre_es
FROM base
INNER JOIN mm 
ON mm.uid_local=base.uid
INNER JOIN t2
ON mm.uid_foreign=t2.uid WHERE mm.tablenames = 't2'

Thanks in advance.

I could do it using two VIEWS, the first using the code above with the name 'viewtest", and the second with this code:

SELECT base.uid,
GROUP_CONCAT(DISTINCT vi.nombre_es ORDER BY vi.nombre_es SEPARATOR ',') as nombre

FROM base

INNER JOIN viewtest as vi
ON vi.uid=base.uid

GROUP BY uid

Now the question is ¿How can I join this two views in a single view?

Memochipan
  • 3,405
  • 5
  • 35
  • 60

1 Answers1

13

You can use derived tables from queries. Next is an example of how you can use them.

SELECT GROUP_CONCAT( f ) 
FROM (
  SELECT 1 AS f  # <-- QUERY #1
  UNION 
  SELECT 2 AS f  # <-- QUERY #2
  UNION 
  SELECT 3 AS f  # <-- QUERY #3
) AS T

Basically, you can use any SELECT query as an aliased table. Then, you can apply any aggregate functions you need to that aliased query.

Ignacio Cortorreal
  • 146
  • 1
  • 2
  • Thanks Ignacio for your answer, I'm scared about performance: [Derived Tables and Views Performance](http://www.mysqlperformanceblog.com/2006/08/31/derived-tables-and-views-performance/) Do you know other way to do what I need? – Memochipan Aug 17 '11 at 19:54