Jalil Azizian Jalil Azizian - 5 months ago 8
SQL Question

How to get result of two select join statement with one query instead

I would like to retrieve table rows (of tables:

receipt
and
invoice
) by date column of both tables with a single select query.

invoice columns are:

| Bill_number | bill_date | amount_paid | total_amount | customer_id |


receipt columns are:

| Bill_number | bill_date | total_amount | customer_id |


I can retrieve expected data (date of specific) with
select
and
join
clause from one table at a time , but I could not find out how to retrieve/search a particular date in row of both tables in one single query.

Queries work fine individually:

SELECT receipt.*, customer.customer_name
FROM receipt
INNER JOIN customer ON receipt.customer_id = customer.customer_id
WHERE receipt.bill_date = '2016-06-24'

SELECT invoice.*, customer.customer_name
FROM invoice
INNER JOIN customer ON invoice.customer_id = customer.customer_id
WHERE invoice.bill_date = '2016-06-24'


Expected result:

|bill_number| bill_date |amount_paid| total_amount |customer_id| customer_name |
1 2016-06-24 20 20 1 John <-- invoice table
1 2016-06-24 20 1 John <-- receipt table


Thanks a lot .

Answer

You can simply use UNION. If the two tables don't match exactly, you might have to define missing columns (for instance the amount_paid column in this situation):

(SELECT bill_number, bill_date, amount_paid, total_amount, customer.customer_id, customer_name
 FROM receipt
 INNER JOIN customer ON receipt.customer_id = customer.customer_id
 WHERE receipt.bill_date = '2016-06-24')
UNION
(SELECT bill_number, bill_date, NULL AS amount_paid, total_amount, customer.customer_id, customer_name
 FROM invoice
 INNER JOIN customer ON invoice.customer_id = customer.customer_id
 WHERE invoice.bill_date = '2016-06-24')

Here is the link to the MySQL documentation.