CODEWITHSUNDEEP
mysqlsql
stand
stand

Reputation: 139

Full join two querys

This query returned 385 strings 

SELECT order_number
FROM `order` 
inner join order_delivery_data on `order`.order_id = order_delivery_data.order_id
where order_status = 'delivered' AND order_statusUpdatedAt >= DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY)
and order_delivery_data_name in ('London')

this returned 74 strings

SELECT order_number
FROM `order` 
inner join order_delivery_data on `order`.order_id = order_delivery_data.order_id
where order_status = 'delivered' AND order_statusUpdatedAt BETWEEN  DATE_SUB(CURRENT_DATE, INTERVAL 10 DAY) 
AND  DATE_SUB(CURRENT_DATE, INTERVAL 7  DAY)
and order_delivery_data_name in ('London')

How join it, that in that there are two columns

Upvotes: 0

Views: 53

Answers (2)

Danilo Bustos
Danilo Bustos

Reputation: 1093

 try this:
 SELECT t1.order_number as order_t1, t2.order_number as order_t2 FROM
        (       SELECT order_number,`order`.order_id 
                FROM `order` 
                inner join order_delivery_data on `order`.order_id = order_delivery_data.order_id
                where order_status = 'delivered' AND order_statusUpdatedAt BETWEEN  DATE_SUB(CURRENT_DATE, INTERVAL 10 DAY) 
                AND  DATE_SUB(CURRENT_DATE, INTERVAL 7  DAY)
                and order_delivery_data_name in ('London')

        ) as t1
        LEFT JOIN
        (SELECT order_number,`order`.order_id 
                FROM `order` 
                inner join order_delivery_data on `order`.order_id = order_delivery_data.order_id
                where order_status = 'delivered' AND order_statusUpdatedAt BETWEEN  DATE_SUB(CURRENT_DATE, INTERVAL 10 DAY) 
                AND  DATE_SUB(CURRENT_DATE, INTERVAL 7  DAY)
                and order_delivery_data_name in ('London')
        ) as t2
        ON t1.order_id  = t2.order_id
    UNION ALL
        SELECT t1.order_number as order_t1, t2.order_number as order_t2 FROM
        (       SELECT order_number,`order`.order_id 
                FROM `order` 
                inner join order_delivery_data on `order`.order_id = order_delivery_data.order_id
                where order_status = 'delivered' AND order_statusUpdatedAt BETWEEN  DATE_SUB(CURRENT_DATE, INTERVAL 10 DAY) 
                AND  DATE_SUB(CURRENT_DATE, INTERVAL 7  DAY)
                and order_delivery_data_name in ('London')

        ) as t1
        RIGHT JOIN
        (SELECT order_number,`order`.order_id 
                FROM `order` 
                inner join order_delivery_data on `order`.order_id = order_delivery_data.order_id
                where order_status = 'delivered' AND order_statusUpdatedAt BETWEEN  DATE_SUB(CURRENT_DATE, INTERVAL 10 DAY) 
                AND  DATE_SUB(CURRENT_DATE, INTERVAL 7  DAY)
                and order_delivery_data_name in ('London')
        ) as t2
        ON t1.order_id  = t2.order_id;

Upvotes: 1

milosdju
milosdju

Reputation: 803

You need to use FULL JOIN, i.e. UNION ALL when using mysql:

first_query UNION ALL second_query

Upvotes: 0

Related Questions