Union All and join query not working

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
$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, 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)";	

Open in new window



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
"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'  ";

Open in new window

 
i attached the table sql file.
I appreciate your's help.
Thank you
LVL 2
Amita SinghWeb DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

johnsoneSenior Oracle DBACommented:
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

Open 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.
0
slightwv (䄆 Netminder) Commented:
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

Open in new window


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

http://www.sqlfiddle.com/#!9/129ba4d/1
0
rajeevnandanmishraCommented:
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)";	

Open 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'  ";

Open in new window

It may work.
0
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

awking00Information Technology SpecialistCommented:
Is the clients field in the psb_clients_bbf table the same as the client field in the psb_billing table?
0
awking00Information Technology SpecialistCommented:
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'
0
Amita SinghWeb DeveloperAuthor Commented:
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
0
NerdsOfTechTechnology ScientistCommented:
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?
0
NerdsOfTechTechnology ScientistCommented:
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'
";

Open in new window

0
awking00Information Technology SpecialistCommented:
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.
0
Amita SinghWeb DeveloperAuthor Commented:
Hi  awking00,

yes the client data is same all the 3 tables.
0
awking00Information Technology SpecialistCommented:
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'
0
Amita SinghWeb DeveloperAuthor Commented:
"SELECT a.* FROM ((
    SELECT PB.invoice_date,
        PB.invoice_no,
        PB.job_no,
        PB.client,
        PB.branch,
        PB.cr_note,
        (
            PB.total_gst * PB.conversion_rate
        ) AS bill_ouputgst,
        PB.invoice_amount_wt_gst,
        PB.company,
        PB.currency AS bill_currency,
        PB.conversion_rate AS b_exchrate,
        '' AS total_expense_including_gst,
            '' AS total_expense_including_gst_inr,
        '' AS exp_inputgst,
        '' AS je_invoice,
        '' AS exp_currency,
        '' AS je_exchrate,
        '' AS je_jobno,
        '1' AS type,
        PB.created_date
    FROM
        psb_billing PB
    WHERE
        PB.invoice_date BETWEEN '".$datefrom."' AND '".$dateto."' ".$condition."
    ORDER BY
        PB.created_date ASC
)
UNION ALL
    (
    SELECT PB.invoice_date,
        PB.invoice_no,
        PB.job_no,
        PB.client,
        PB.branch,
        PB.cr_note,
        '' AS bill_ouputgst,
        '' AS invoice_amount_wt_gst,
        PB.company,
        '' AS bill_currency,
        '' AS b_exchrate,
        JE.total_expense_including_gst,
            (JE.total_expense_including_gst* JE.conversion_rate) AS total_expense_including_gst_inr,
        (JE.total_gst * JE.conversion_rate) AS exp_inputgst,
        JE.invoice_no AS je_invoice,
        JE.currency AS exp_currency,
        JE.conversion_rate AS je_exchrate,
        JE.job_no AS je_jobno,
        '2' AS type,
        JE.created_date
    FROM job_expenses JE
       
    INNER JOIN psb_billing PB ON
        PB.b_id = JE.invoice_no
    WHERE
        JE.invoice_closed_date BETWEEN '".$exp_datefrom."' AND '".$exp_dateto."' and JE.total_expense_including_gst>0 and JE.conversion_rate>=1 and JE.company = '$comp' ".$condition1."
    ORDER BY
        JE.created_date ASC
)
) As a
ORDER BY a.created_date ASC";
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.