Unable to perform union, getting #1248 - Every derived table must have its own alias error

Question

I have these two queries

select 
    t . *, events.event_time as last_time
from
    events,
(
    (
        select 
            bonding.type,
                bonding.global_id2 as target_post,
                bonding.target_id as on_whose_post,
                GROUP_CONCAT(bonding.shooter_id) as shooter_ids,
                GROUP_CONCAT(bonding.what_global_id) as shooted_what,
                MAX(bonding.what_global_id) as last,
                'bonding' as flag
        from
            bonding
        where
            bonding.type = 1
                and bonding.shooter_id in (select 
                    `user2`
                from
                    relation_table
                where
                    `user1` = 192)
        group by bonding.global_id2
    ) 
    union 
    (
    select 
            bonding.type,
                bonding.global_id2 as target_post,
                bonding.target_id as on_whose_post,
                GROUP_CONCAT(bonding.shooter_id) as shooter_ids,
                GROUP_CONCAT(bonding.what_global_id) as shooted_what,
                MAX(bonding.what_global_id) as last,
                'bonding' as flag
        from
            bonding
        where
            bonding.type = 2
                and bonding.shooter_id in (select 
                    `user2`
                from
                    relation_table
                where
                    `user1` = 192)
        group by bonding.global_id2
    ) 
    union 
    (
    select 
            bonding.type,
                bonding.global_id2 as target_post,
                bonding.target_id as on_whose_post,
                GROUP_CONCAT(bonding.shooter_id) as shooter_ids,
                GROUP_CONCAT(bonding.what_global_id) as shooted_what,
                MAX(bonding.what_global_id) as last,
                'bonding' as flag
        from
            bonding
        where
            bonding.type = 5
                and bonding.shooter_id in (select 
                    `user2`
                from
                    relation_table
                where
                    `user1` = 192)
        group by bonding.global_id2
    ) 
    union 
    (
    select 
            bonding.type,
                bonding.global_id2 as target_post,
                bonding.target_id as on_whose_post,
                GROUP_CONCAT(bonding.shooter_id) as shooter_ids,
                GROUP_CONCAT(bonding.what_global_id) as shooted_what,
                MAX(bonding.what_global_id) as last,
                'bonding' as flag
        from
            bonding
        where
            bonding.type = 9
                and bonding.shooter_id in (select 
                    `user2`
                from
                    relation_table
                where
                    `user1` = 192)
        group by bonding.global_id2
    ) 
    union 
    (
    select 
            bonding.type,
                bonding.global_id2 as target_post,
                bonding.target_id as on_whose_post,
                GROUP_CONCAT(bonding.shooter_id) as shooter_ids,
                GROUP_CONCAT(bonding.what_global_id) as shooted_what,
                MAX(bonding.what_global_id) as last,
                'bonding' as flag
        from
            bonding
        where
            bonding.type = 10
                and bonding.shooter_id in (select 
                    `user2`
                from
                    relation_table
                where
                    `user1` = 192)
        group by bonding.global_id2
    )

)as t where events.global_id = t1.last

and other one :-

SELECT 
    post_stream.type,
    post_stream.ref_global_id as target_post,
    post_stream.user_id as on_whose_post,
    post_stream.user_id as shooter_ids,
    post_stream.ref_global_id as shooted_what,
    post_stream.ref_global_id as last,
    'stream' as flag,
    events.event_time as last_time
FROM
    post_stream,
    events,
    relation_table
WHERE
    events.global_id = post_stream.ref_global_id
        and post_stream.type IN (2 , 3, 7, 8)
        AND post_stream.user_id = relation_table.user2
        AND relation_table.user1 = 192

now I need to perform a join on both the queries to get combined result, but it is giving Every derived table must have its own alias error, where I should put an alias for derived table these two queries are running with no errors when run separately.

It's asking you to alias the sub-queries in your first query (each of the `UNION ( SELECT... )` statements including the first `SELECT`. You can also alias the second query too in exactly the same way. — dash, Nov 25 '12 at 12:41
I was some how able to solve problem but this query is taking like 40 sec to complete.... — Manish, Nov 25 '12 at 12:54
You need to stop using the `FROM a, b, c WHERE ... AND ...` notation and use the explicit `FROM a JOIN b ON ... JOIN c ON ...` notation. You need to know the other exists so you can read queries written in the 90s when support for JOIN wasn't always available. You should never write it yourself. — Jonathan Leffler, Nov 25 '12 at 12:54
Note that the first SQL is in the form: `SELECT stuff FROM a, (SELECT ...) AS t`. This means it is a Cartesian product of the two expressions; every row from the `a` table is matched with every row from the `(SELECT ...) AS t` query. This is not good. You've not told us how the two queries are to be joined. Which columns in the result set of the second query are to be joined with which columns in the result set of the first query? — Jonathan Leffler, Nov 25 '12 at 13:03
Thank for suggestion I know that my 1st query is pathetically written. I have edited my 1st query please have a look... — Manish, Nov 25 '12 at 13:07

Jonathan Leffler · Accepted Answer · 2012-11-25T13:31:34.050

The first query is about 116 lines long as formatted, and contains a 5-way UNION sub-query. Those 5 sub-queries appear to be identical apart from one value in the WHERE clause. This rewrite dramatically simplifies the SQL to:

SELECT t.type, t.target_post, t.on_whose_post, t.shooter_ids, t.shooted_what,
       t.last, t.flag, events.event_time AS last_time
  FROM events JOIN
       (SELECT bonding.type,
               bonding.global_id2 AS target_post,
               bonding.target_id AS on_whose_post,
               GROUP_CONCAT(bonding.shooter_id) AS shooter_ids,
               GROUP_CONCAT(bonding.what_global_id) AS shooted_what,
               MAX(bonding.what_global_id) AS last,
               'bonding' AS flag
          FROM bonding
         WHERE bonding.TYPE IN (1, 2, 5, 9, 10)
           AND bonding.shooter_id IN (SELECT user2 FROM relation_table WHERE user1 = 192)
         GROUP BY bonding.global_id2
        ) AS t
     ON events.global_id = t1.last

This will be much easier to combine with the second query. With further revision, I'd probably remove the bonding. prefixes since the only table in the main sub-query is bonding.

The second query should be rewritten using JOIN notation too:

SELECT p.type          AS type,
       p.ref_global_id AS target_post,
       p.user_id       AS on_whose_post,
       p.user_id       AS shooter_ids,
       p.ref_global_id AS shooted_what,
       p.ref_global_id AS last,
       'stream'        AS flag,
       e.event_time    AS last_time
  FROM post_stream    AS p
  JOIN events         AS e ON e.global_id = p.ref_global_id
  JOIN relation_table AS r ON p.user_id = r.user2
 WHERE r.user1 = 192
   AND post_stream.type IN (2 , 3, 7, 8)

Questions:

Are you sure that on_whose_post and shooter_ids should be from the same column?
Are you sure that shooted_what and last should be from the same column?

There could be valid (and not too far-fetched) reasons for doing that — but it isn't immediately obvious.

Unfortunately, we've not been told how to join the data from the first query above with the second query. There seem to be quite a lot of columns in common; only the OP can determine what's required.

using IN (1,2,5,9,10) will not give me required result as I want to separately group data having {bonding.type =1,bonding.type =2,bonding.type =5} using IN will group every thing together. — Manish, Nov 25 '12 at 13:20
I am new to MySQL, just out of curiosity I am asking that is using "join a on b" statements is better over using "from a,b" or they both are same — Manish, Nov 25 '12 at 13:24
That is nonsense. There'll be one row in the output for type = 1, one for type = 2, etc. You're working with MySQL which has screwball rules for how the non-aggregate values in the subquery will be selected at quasi-random, but I left that part of your query untouched. Be wary of non-aggregate columns in a query that are not listed in the GROUP BY clause; they are a portability liability and are quite possibly non-deterministic. — Jonathan Leffler, Nov 25 '12 at 13:25
One primary benefit is that JOIN is not syntactically complete unless you add an ON (or USING) clause to complete the join. That means you do not run into the problem of Cartesian products due to a missing join condition. It also keeps the joining conditions with the joined tables, leaving filter conditions only in the WHERE clause. It is generally superior in just about every respect to the original (SQL-86, SQL-89) notation; it was added to SQL-92. — Jonathan Leffler, Nov 25 '12 at 13:27

Unable to perform union, getting #1248 - Every derived table must have its own alias error

1 Answers1