Solved

Join in table with table A OR if theres no information with table B OR if no info then table C

Posted on 2013-12-17
14
281 Views
Last Modified: 2013-12-17
Dear Experts,

I Need help in creating a correct SQL Statement.
thats the logic:
One Table Fault log. And 3 tables: Service invoice, sales Invoice and Sales Shipments.
The fault log includes my basic information. Out of ONE of the other tables I get my detail information.


I would like to select something like:

Fault_Log.*, (Service_Invoices.Amount OR Sales_Invoices.Ampunt OR Sales Shipments.Amount)
from
Fault_Log left outer join (Service_Invoices OR Sales_Invoices.Ampunt OR Sales Shipments.Amount)
ON Fault_Log.[Document_No_] = Service_Invoices.[Service_Quote_No_]
or if you cannot find an entry there then
ON Fault_Log.[Document_No_] = Sales_Invoices.[Service_Quote_No_]
or of you cannot find an entry there then
ON Fault_Log.[Document_No_] = SalesShipments.[Document_No_]

Hope you understand what I Need. Thank you for your help!
0
Comment
Question by:arthrex
  • 6
  • 5
  • 2
14 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39723790
SELECT flog.*, COALESCE(svi.Amount,0) + COALESCE(sai.Ampunt, 0) + COALESCE(ship.Amount,0) as the_amount
from  Fault_Log flog
	LEFT JOIN Service_Invoices svi ON flog.[Document_No_] = svi.[Service_Quote_No_]
	LEFT JOIN Sales_Invoices sai ON flog.[Document_No_] = sai.[Service_Quote_No_]
	LEFT JOIN SalesShipments ship ON flog.[Document_No_] = ship.[Document_No_]

Open in new window


Might not be a bad idea though to return the three amounts in separate columns, so you can handle any exceptions like what to do if more then one column is populated.
0
 

Author Comment

by:arthrex
ID: 39723798
Thank you very much jimhorn!
But what are with those entries in fault log not having any detail? they won't be selected at all right?
How can I add them?
And another question:
how do I select any info which is not numeric? I cannot use the sum function you've used then. (I know that wasn't the question, but I also Need to select strings, so I ask)
Thank you very much!
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39723807
>But what are with those entries in fault log not having any detail? they won't be selected at all right?  How can I add them?

If there is no fault_log row, then how would you know what rows 'match' in the other three tables?

>how do I select any info which is not numeric?
That's a bigger problem, if you are attempting to get a numeric value out of a character column, that may or may not have non-numeric values like 'banana' in them.   Explain for us the logic in how you wish to handle this.
0
 

Author Comment

by:arthrex
ID: 39723826
> If there is no fault_log row, then how would you know what rows 'match' in the other three tables?
I meant, there is a fault log row but no detail in any of the other three tables. That's the reason I've used left outer join before.

> Explain for us the logic in how you wish to handle this.
Actually I would like to show the amount and the column "external info" in case we found the details in the sales invoice or Service invoice table beside it. Or the column "vendor info" if we find the details in the shipment table.
On top a column would be great to know where the info comes from (sales or service invoices or shipments).
Thank you
0
 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 300 total points
ID: 39723864
In that case you'll need something like this..
SELECT flog.*, 
	COALESCE(svi.Amount,0) + COALESCE(sai.Ampunt, 0) + COALESCE(ship.Amount,0) as amount, 
	CASE 
		WHEN COALESCE(svi.Amount,0) <> 0 THEN ''
		WHEN COALESCE(sai.Amount, 0) <> 0 THEN 'External info'
		WHEN COALESCE(ship.Amount,0) <> 0 THEN 'Vendor info' END as source
from  Fault_Log flog
	LEFT JOIN Service_Invoices svi ON flog.[Document_No_] = svi.[Service_Quote_No_]
	LEFT JOIN Sales_Invoices sai ON flog.[Document_No_] = sai.[Service_Quote_No_]
	LEFT JOIN SalesShipments ship ON flog.[Document_No_] = ship.[Document_No_]

Open in new window

0
 

Author Comment

by:arthrex
ID: 39723877
yes perfect. but can I also do something like that?:

SELECT flog.*, 
	COALESCE(svi.Amount,0) + COALESCE(sai.Ampunt, 0) + COALESCE(ship.Amount,0) as amount, 

	CASE 
		WHEN COALESCE(svi.Amount,0) <> 0 THEN select svi.external_info
		WHEN COALESCE(sai.Amount, 0) <> 0 THEN select sai.external_info as isai_external_info
		WHEN COALESCE(ship.Amount,0) <> 0 THEN select ship.vendor_info END as vendor		
from  Fault_Log flog
	LEFT JOIN Service_Invoices svi ON flog.[Document_No_] = svi.[Service_Quote_No_]
	LEFT JOIN Sales_Invoices sai ON flog.[Document_No_] = sai.[Service_Quote_No_]
	LEFT JOIN SalesShipments ship ON flog.[Document_No_] = ship.[Document_No_]

Open in new window

0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 300 total points
ID: 39723880
CASE 
   WHEN COALESCE(svi.Amount,0) <> 0 THEN select svi.external_info
   WHEN COALESCE(sai.Amount, 0) <> 0 THEN select sai.external_info as isai_external_info
   WHEN COALESCE(ship.Amount,0) <> 0 THEN select ship.vendor_info END as vendor		

Open in new window

Nope, the CASE block works for one and only one column.  
You'll have to split isai_external_info into a separate column.

Also, remove the 'select' after then THEN's.
0
 
LVL 8

Assisted Solution

by:Surrano
Surrano earned 200 total points
ID: 39723885
Or maybe you thought something like this; also works for non-numeric data and also handles an amount of 0 differently from nonexistent detail (null):

SELECT flog.*, 
	COALESCE(svi.Amount, sai.Ampunt, ship.Amount, null) as amount, 
	CASE 
		WHEN svi.Amount is not null THEN 'External info'
		WHEN sai.Amount is not null THEN 'External info'
		WHEN ship.Amount is not null THEN 'Vendor info' END as source
from  Fault_Log flog
	LEFT JOIN Service_Invoices svi ON flog.[Document_No_] = svi.[Service_Quote_No_]
	LEFT JOIN Sales_Invoices sai ON flog.[Document_No_] = sai.[Service_Quote_No_]
	LEFT JOIN SalesShipments ship ON flog.[Document_No_] = ship.[Document_No_];

Open in new window

0
 

Author Comment

by:arthrex
ID: 39724043
sorry. not sure if I got that right. is that correct?
SELECT flog.*, 
	COALESCE(svi.Amount,0) + COALESCE(sai.Ampunt, 0) + COALESCE(ship.Amount,0) as amount, 

	CASE 
		WHEN COALESCE(svi.Amount,0) <> 0 THEN  svi.external_info
		WHEN COALESCE(sai.Amount, 0) <> 0 THEN  sai.external_info as isai_external_info
		WHEN COALESCE(ship.Amount,0) <> 0 THEN  ship.vendor_info END as vendor		
	CASE 
		WHEN COALESCE(svi.Amount,0) <> 0 THEN ''
		WHEN COALESCE(sai.Amount, 0) <> 0 THEN 'External info'
		WHEN COALESCE(ship.Amount,0) <> 0 THEN 'Vendor info' END as source

from  Fault_Log flog
	LEFT JOIN Service_Invoices svi ON flog.[Document_No_] = svi.[Service_Quote_No_]
	LEFT JOIN Sales_Invoices sai ON flog.[Document_No_] = sai.[Service_Quote_No_]
	LEFT JOIN SalesShipments ship ON flog.[Document_No_] = ship.[Document_No_]

Open in new window

0
 
LVL 8

Accepted Solution

by:
Surrano earned 200 total points
ID: 39724053
If you wish to go that way make sure you use only one field name after "END". Others make no sense, i.e.:

SELECT flog.*, 
	COALESCE(svi.Amount, sai.Ampunt, ship.Amount, null) as amount, 
	CASE 
		WHEN svi.Amount is not null THEN svi.external_info
		WHEN sai.Amount is not null THEN sai.external_info
		WHEN ship.Amount is not null THEN ship.vendor_info END as info
	CASE 
		WHEN svi.Amount is not null THEN ''
		WHEN sai.Amount is not null THEN 'External info'
		WHEN ship.Amount is not null THEN 'Vendor info' END as source
from  Fault_Log flog
	LEFT JOIN Service_Invoices svi ON flog.[Document_No_] = svi.[Service_Quote_No_]
	LEFT JOIN Sales_Invoices sai ON flog.[Document_No_] = sai.[Service_Quote_No_]
	LEFT JOIN SalesShipments ship ON flog.[Document_No_] = ship.[Document_No_];

Open in new window

0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39724074
btw arthrex - I wrote an article awhile back on SQL Server CASE solutions that will help you with a lot of these syntax questions.
0
 

Author Comment

by:arthrex
ID: 39724094
Thank you very much!!
0
 

Author Comment

by:arthrex
ID: 39724098
oh no!!! so sorry jimhorn!
I thought you were the last who wrote!
Now I only gave surrano the points!
can I split this somehow afterwards as you answered so patiently all my questions jimhorn!
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.‚Äč
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

861 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now