Union All and join query not working

Without the error, this may not be the only issue.  You have 3 select statements.  I don't think the parenthesis is necessary, but your issue is the number and datatype of what you are selecting.  Just look at the 3 select lists:

SELECT invoice_amount_wt_gst, cr_note,         company, client, invoice_no, job_no
SELECT on_ac,                 tds,             client,  job_no, invoice_no
SELECT bf_amount,             bf_credit_amount

Select allOpen in new window

The datatype and number of columns must match between the 3 select lists.

A UNION ALL is not a join.  It will just give you the results of multiple queries concatenated together.  There really isn't a join equivalent.

What is the error?

In a UNION or UNION ALL you need the same number of columns for each query

For example:

select col1, col2 from tab1
union all
select col1 from tab1

Select allOpen in new window

returns:  The used SELECT statements have a different number of columns  

http://www.sqlfiddle.com/#!9/129ba4d/1

Hi,

Please share the table SQL file (with key columns mentioned).

By looking at two queries, following is my feedback
1. For using UNION operator, you must have same number of columns in all queries. i am not sure about table structure and column meanings, but you can see the output using following query:

$sql_search = "(SELECT invoice_amount_wt_gst, cr_note, company, client, invoice_no, job_no FROM psb_billing WHERE invoice_date BETWEEN '$recv_date' AND '$invoice_end_date' and currency = '$currency' and company = $comp)
	UNION ALL
(SELECT on_ac, tds, company, client, invoice_no, job_no  FROM receiving_report WHERE receiving_date BETWEEN '$recv_date' AND '$invoice_end_date' and currency = '$currency' and company = $comp)
	UNION ALL
(SELECT bf_amount, bf_credit_amount, clients, '--', '--' FROM psb_clients_bbf WHERE currency = '$currency' and bf_company = $comp)";	

Select allOpen in new window

2. Kindly change your join condition to

SELECT PB.invoice_amount_wt_gst, PB.cr_note, PB.company, PB.client, PB.invoice_no, PB.job_no, PB.invoice_date, PB.currency
    , R.receiving_date, R.client, R.on_ac, R.tds, R.currency
    , BB.clients, BB.bf_amount, BB.bf_credit_amount, BB.currency, BB.bf_company 
FROM psb_billing PB left join receiving_report R on PB.company = R.company 
left join psb_clients_bbf BB on PB.company = BB.bf_company and PB.client = BB.clients 
WHERE PB.invoice_date BETWEEN '$recv_date' AND '$invoice_end_date'  ";

Select allOpen in new window

It may work.

Is the clients field in the psb_clients_bbf table the same as the client field in the psb_billing table?

SELECT b.invoice_amount_wt_gst, b.cr_note, b.company, b.client, b.invoice_no, b.job_no,
r.on_ac, r.tds, c.bf_amount, c.bf_credit_amount, c.clients
FROM psb_billing b
INNER JOIN
receiving_report r
ON b.company = r.company
AND b.client = r.client
AND b.invoice_no = r.invoice_no
AND b.job_no = r.job_no
AND b.currency = r.currency
LEFT JOIN
psb_clients_bbf c
ON b.currency = c.currency
AND b.company = c.company
WHERE b.currency = '$currency'
AND b.company = $comp
AND b.invoice_date BETWEEN '$recv_date' AND '$invoice_end_date'
AND r.receiving_date BETWEEN '$recv_date' AND '$invoice_end_date'

Hi  awking00,

Thank for the reply. I try your query its running fine but its repeat records its show "130800" records. i used DISTINCT but the result are same.
is any way to not repeat the records ?
Thanx

Although the answer to why your UNION isn't working is due to the SELECTs not giving you the same columns is apparent;
your JOIN is not working because your ON is not filtering by the correct column/ID's, thus, it is returning multiple rows per invoice.

So, it either sounds like you want to list all records from the tables matching certain search criteria or list all invoices (and correlated data) which match that search criteria.

So which is it?

Presuming that you want the latter of the two results from my previous question,

ON psb_billing.company = receiving_report.company

Is one example of why your results are being multiplied together.

Properly correlate the data, you will need to use 1:1 data such as an invoice_no.

on psb_billing.invoice_no = receiving_report.invoice_no

Assuming that invoice_no allows you to correlate all three tables:

"
SELECT 
	  PB.invoice_amount_wt_gst
	, PB.cr_note
	, PB.company
	, PB.client
	, PB.invoice_no
	, PB.job_no
	, PB.invoice_date
	, PB.currency

	, R.receiving_date
	, R.on_ac
	, R.tds

	, BB.bf_amount
	, BB.bf_credit_amount

	FROM psb_billing PB 
	
	LEFT join receiving_report R 
	on PB.invoice_no = R.invoice_no 
	
	LEFT JOIN join psb_clients_bbf BB 
	on PB.invoice_no = BB.invoice_no 
	
	WHERE PB.invoice_date 
	BETWEEN '$recv_date' AND '$invoice_end_date'
";

Select allOpen in new window

The reason I asked about the clients field in the psb_clients_bbf table being the same data as the client field in the other two tables is that if it's so, another join condition needs to be added. Without it, duplicate values could be created.

Hi  awking00,

yes the client data is same all the 3 tables.

With added join condition -
SELECT b.invoice_amount_wt_gst, b.cr_note, b.company, b.client, b.invoice_no, b.job_no,
 r.on_ac, r.tds, c.bf_amount, c.bf_credit_amount
 FROM psb_billing b
 INNER JOIN
 receiving_report r
 ON b.company = r.company
 AND b.client = r.client
 AND b.invoice_no = r.invoice_no
 AND b.job_no = r.job_no
 AND b.currency = r.currency
 INNER JOIN
 psb_clients_bbf c
 ON b.currency = c.currency
 AND b.company = c.company
 AND b.client = c.clients
 WHERE b.currency = '$currency'
 AND b.company = $comp
 AND b.invoice_date BETWEEN '$recv_date' AND '$invoice_end_date'
 AND r.receiving_date BETWEEN '$recv_date' AND '$invoice_end_date'