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

<pre><code class="code-1 nolinks" id="code-20-42681455-1">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
</code></pre>
The datatype and number of columns must match between the 3 select lists. 
 
A UNION ALL is not a join. &nbsp;It will just give you the results of multiple queries concatenated together. &nbsp;There really isn't a join equivalent.
</div>

<div>
What is the error? 
 
In a UNION or UNION ALL you need the same number of columns for each query 
 
For example: 

<pre><code class="code-1 nolinks" id="code-20-42681458-1">select col1, col2 from tab1
union all
select col1 from tab1
</code></pre>
 
returns: &nbsp;The used SELECT statements have a different number of columns &nbsp; 
 
<a href="http://www.sqlfiddle.com/#!9/129ba4d/1" rel="ugc">http://www.sqlfiddle.com/#!9/129ba4d/1</a>
</div>

<div>
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: 

<pre><code class="code-1 nolinks" id="code-20-42681459-1">$sql_search = &quot;(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)&quot;;	
</code></pre>
 
2. Kindly change your join condition to 

<pre><code class="code-1 nolinks" id="code-20-42681459-2">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' &quot;;
</code></pre>
It may work.
</div>

<div>
Is the clients field in the psb_clients_bbf table the same as the client field in the psb_billing table?
</div>

<div>
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'
</div>

<div>
Hi &nbsp;awking00, 
 
Thank for the reply. I try your query its running fine but its repeat records its show &quot;130800&quot; records. i used DISTINCT but the result are same. 
is any way to not repeat the records ? 
Thanx
</div>

<div>
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?
</div>

<div>
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: 
 

<pre><code class="code-10 nolinks" id="code-20-42682386-1">&quot;
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'
&quot;;
</code></pre>
</div>

<div>
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.
</div>

<div>
Hi &nbsp;awking00, 
 
yes the client data is same all the 3 tables.
</div>

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

<div>
<div class="content wysiwyg-content">
Hi, 
 
I want to fetch data from 3 table for time periods , i am using UNION All but its show error. 
My sql code is 

<pre><code class="code-1 nolinks" id="code-10-29117981-1">$sql_search = &quot;(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, client, job_no, invoice_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 FROM psb_clients_bbf WHERE currency = '$currency' and bf_company = $comp)&quot;;	
</code></pre>
 
 
i don't understand where i am wrong. and when i am using join query its show me lots of data. i think Its also fetch the wrong data. 
my join query is 

<pre><code class="code-1 nolinks" id="code-10-29117981-2">&quot;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.client = BB.clients WHERE PB.invoice_date BETWEEN '$recv_date' AND '$invoice_end_date' &quot;;
</code></pre>
 &nbsp; 
i attached the table sql file. 
I appreciate your's help. 
Thank you
</div>
</div>

Hi,

I want to fetch data from 3 table for time periods , i am using UNION All but its show error.
My sql code is
(CODE) 

i don't understand where i am wrong. and when i am using join query its show me lots of data. i think Its also fetch the wrong data.
my join query is 
(CODE)  
i attached the table sql file.
I appreciate your's help.
Thank you

Union All and join query not working

Databases are organized collections of data, most commonly accessed through management systems including schemas, tables, queries and processes that allow users to enter and manipulate the information or utilize it in other fashions, such as with web applications or for reporting purposes.

Databases

MySQL is an open source, relational database management system that runs as a server providing multi-user access to a number of databases. Acquired by Oracle in 2009, it is frequently used in combination with PHP installations, powering most of the WordPress installations.

MySQL Server

SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.