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.
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:
on_whose_post and shooter_ids should be from the same column? 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.