Mysql: Union of a Left Join with a Right Join

MySQL: Union of a Left Join with a Right Join

select a.id, t1.column_1, t2.column_2
from (
    select id from t1
    union 
    select id from t2
) a
left outer join t1 on a.id = t1.id
left outer join t2 on a.id = t2.id

MySQL UNION ALL with LEFT JOIN

So finally got there with the below, appreciate the direction halfer

SELECT
    comments.*, users.company, users.contact_person, users.email
    FROM
        comments
    LEFT JOIN users ON users.user_id = comments.user_id
    WHERE
        comment_id = %s
UNION ALL
SELECT activity.*, users.company, users.contact_person, users.email
    FROM
        activity
    LEFT JOIN users ON users.user_id = activity.user_id
    WHERE
        comment_id = %s
ORDER BY
    timestamp ASC

MySQL Left Join Right Join union returns incorrect data

You do the joins on workoutDate = weighInDate. This gives you combinations of unrelated rows, which remain in the result set, even with unrelated user names.

To fix this, you must join on both dates and usernames, e.g.

LEFT JOIN sprint AS metric ON weight.weighInDate = metric.workoutDate
                              and weight.username = metric.username

similarly on right join, of course.

---

Added to the answer because it helps illustrate the cause of the problem. The @OlafDietsche has the correct solution. I just wanted you to see where the actual wrong data came from.

To show you the problem with your join this record:

| testuser | 2016-10-17  |            3.40

is joining to this record

| jdoe     | 2016-10-17  |    160 |

because you only joined on date.

Order by left outer join union right outer join

(
SELECT * 
FROM groups
LEFT OUTER JOIN group_questions
ON groups.id = group_questions.group_id 
WHERE group_questions.group_id = 1
)
UNION
(
SELECT * 
FROM groups
RIGHT OUTER JOIN group_questions 
ON groups.id = group_questions.group_id 
WHERE group_questions.group_id = 1 
)
ORDER BY id DESC

You must sort combined rowset after UNION. And you must refer to the column name of combined rowset (which now does not refer to any source table of any separate subquery).

In this particular case the parenthesis wrapped separate subqueries are formally excess and may be removed. But I recommend to use them always - this removes possible ambiguity.

How to use join and union for sub queries in mysql

You need to make a couple of changes in order for this query to work:

• Wrap inner SELECT queries into another SELECT * query to make JOIN work, e.g. () SP LEFT JOIN () SP is not a valid syntax. Instead, use SELECT * FROM (..) SP JOIN (..) FP
• Remove SP.* from outer SELECT as SP and FP are only visible to inner queries, use SELECT * instead
• Remove WHERE SP.Service=FP.Service from outer WHERE clause as again, SP and FP won't be visible.

The below query should work:

SELECT *
FROM (
(
SELECT * FROM
(SELECT Service, SUM(Processed) as Second_Period, COUNT(Processed) as TRX_SP
FROM pay
WHERE Status1='Processed' AND Dataime BETWEEN '2017-05-07 00:00:00' and '2017-05-14 00:00:00' 
GROUP BY Service) SP
RIGHT JOIN
(SELECT Service, SUM(Processed) as First_Period, COUNT(Processed) as TRX_FP
FROM pay
WHERE Status1='Processed' AND Dataime BETWEEN '2017-05-01 00:00:00' and '2017-05-06 00:00:00' 
GROUP BY Service) FP USING (Service))
UNION ALL
(
SELECT * FROM
(SELECT Service, SUM(Processed) as Second_Period, COUNT(Processed) as TRX_SP
FROM pay
WHERE Status1='Processed' AND Dataime BETWEEN '2017-05-07 00:00:00' and '2017-05-14 00:00:00' 
GROUP BY Service) SP
LEFT OUTER JOIN
(SELECT Service, SUM(Processed) as First_Period, COUNT(Processed) as TRX_FP
FROM pay
WHERE Status1='Processed' AND Dataime BETWEEN '2017-05-01 00:00:00' and '2017-05-06 00:00:00' 
GROUP BY Service) FP USING (Service) ))  as tbl2
GROUP BY Service
Order BY Service

update

You can't use function in USING clause, so you need to alias that column and use the alias in USING, e.g.:

SELECT *
FROM (
(
SELECT * FROM
(SELECT DAYNAME(Dataime) as 'day', SUM(Processed) as Second_Period, COUNT(Processed) as TRX_SP
FROM pay
WHERE Status1='Processed' AND Dataime BETWEEN '2017-05-07 00:00:00' and '2017-05-14 00:00:00' 
GROUP BY DAYNAME(Dataime)) SP
RIGHT JOIN
(SELECT DAYNAME(Dataime) as 'day', SUM(Processed) as First_Period, COUNT(Processed) as TRX_FP
FROM pay
WHERE Status1='Processed' AND Dataime BETWEEN '2017-05-01 00:00:00' and '2017-05-06 00:00:00' 
GROUP BY DAYNAME(Dataime)) FP USING(`day`))
UNION ALL
(
SELECT * FROM
(SELECT DAYNAME(Dataime) as `day`, SUM(Processed) as Second_Period, COUNT(Processed) as TRX_SP
FROM pay
WHERE Status1='Processed' AND Dataime BETWEEN '2017-05-07 00:00:00' and '2017-05-14 00:00:00' 
GROUP BY DAYNAME(Dataime)) SP
LEFT OUTER JOIN
(SELECT DAYNAME(Dataime) as `day`, SUM(Processed) as First_Period, COUNT(Processed) as TRX_FP
FROM pay
WHERE Status1='Processed' AND Dataime BETWEEN '2017-05-01 00:00:00' and '2017-05-06 00:00:00' 
GROUP BY DAYNAME(Dataime)) FP USING (`day`) ))  as tbl2
GROUP BY `day`
Order BY `day`

Combining Left & Right Join in mysql query

The only you can do that is by using UNION. MySQL doesn't support FULL JOINjust like in MSSQL.

SELECT * 
FROM tbl1 t1 
       LEFT JOIN tbl2 t2
          ON t1.col = t2.col
UNION 
SELECT * 
FROM tbl1 t1 
       RIGHT JOIN tbl2 t2 
          ON t1.col>= t2.<col

SEE HERE: Simulating FULL JOIN in MYSQL

By the way, UNION has optional keyword ALL,when the ALL is omitted, UNION automatically selects DISTINCT rows from the resultset.

EXAMLE:

SELECT *
FROM   tableA
UNION ALL
SELECT *
FROM   tableA

this can result duplicates rows

ColA    ColB
==================
1       John
2       Jade
2       Jade
3       Hello

BUT if you omit the word ALL

SELECT *
FROM   tableA
UNION
SELECT *
FROM   tableA

this can result distinct rows only

ColA    ColB
==================
1       John
2       Jade
3       Hello

MySQL: Using left join and union in one query

If you want to separate rows for a borrow/return combination, then union all is appropriate. If so, I think this does what you want:

SELECT b.isbn, b.member_id, b.staff_id, date_borrowed, 'borrow' as which
FROM tblborrow b
UNION ALL
SELECT b.isbn, b.member_id, r.staff_id, date_returned, 'return' as which
FROM tblreturn r join
     tblborrow b
     on r.borrw_id = b.borrow_id;

Combine three tables with join an two with union

You need a join with subquery for union based on id

Select 
    t1.id, t1.naam, t2.lastname, t.Function 
From 
    t1
Left Join 
    t2 On t2.t1 - id = t1.[id]
Left Join 
    t3 On t3.t2 - id = t2.[id]
Inner Join 
    (Select id, Function
     From t2
     Union 
     Select id, Function
     From t3) t On t.id = t1.id 

FULL OUTER JOIN with union subqueries

I just don't see the point for union all here. Both queries are the same, only the date filter changes. So, use conditional aggregation:

slect ggportal as portal, 
    sum(case when o.ggdate >= '2020-01-01' and o.ggdate < '2021-01-01' then oa.ggamount else 0 end) as amount_2020,
    sum(case when o.ggdate >= '2019-01-01' and o.ggdate < '2020-01-01' then oa.ggamount else 0 end) as amount_2020
from orders as o 
left orderarticles as oa on o.ggauftragsnr = oa.ggauftragsnr 
where o.ggdate >= '2019-01-01' and o.ggdate < '2021-01-01' 
group by ggportal

---

Related Topics