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
259 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
Comment Utility
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
Comment Utility
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
Comment Utility
>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
Comment Utility
> 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
Comment Utility
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
Comment Utility
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 300 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Thank you very much!!
0
 

Author Comment

by:arthrex
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

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.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

744 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

19 Experts available now in Live!

Get 1:1 Help Now