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.
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();
SELECT order_id FROM A WHERE order_group_id = 0
union
SELECT d.order_id FROM A as d, B as e WHERE d.order_group_id = e.order_group_id AND e.paid = 0
You can relate two or more tables by using INNER JOIN. And use ORDER BY (column) ASC/DESC to make ascending or descending order
SELECT order_id FROM A as d INNER JOIN B as e
ON d.order_group_id = e.order_group_id
WHERE d.order_group_id = 0 AND e.paid = 0
ORDER BY order_id ASC