想要通过合并这些查询来在一个查询中获得两个查询的结果

Question

So, I've two tables, one is A(order_id, order_group_id, name), and second one is B(order_group_id, paid).

Table A have few records:

order_id | order_group_id | name
----------------------------------
1        | 0              | A
----------------------------------
2        | 14             | B
----------------------------------
3        | 0              | C
----------------------------------
4        | 15             | D

Table B have few records:

order_group_id | paid
--------------------------
14             | 0              
--------------------------
15             | 1

Now I am running these two queries:

SELECT order_id FROM A WHERE order_group_id = 0

and

SELECT d.order_id FROM A as d, B as e WHERE d.order_group_id = e.order_group_id AND e.paid = 0

First query is giving order_id 1 and 3.

Second query is giving order_id 2.

So, now how to get result of both these queries in single one, also these order_id should be in ascending order.

I'll be very thankful to you, please.

Answer 1

You can achieve it using LEFT JOIN

This query will return all the records which has either paid is 0 or the order_group_id is 0

SELECT order_id, e.paid,  d.order_group_id FROM A as d
LEFT JOIN B as e ON d.order_group_id = e.order_group_id
WHERE e.paid = 0 OR d.order_group_id = 0

In codeignitor you can achieve with the following by

$query = $this->select('SELECT order_id, e.paid,  d.order_group_id')->from('A as d')->join('B as e','d.order_group_id = e.order_group_id','LEFT')->where('e.paid = 0 OR d.order_group_id = 0')->get();

Demo: http://sqlfiddle.com/#!9/2b82f9/8