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

arthrex
arthrex used Ask the Experts™
on
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!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
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.

Author

Commented:
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!
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
>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.
Amazon Web Services

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Author

Commented:
> 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
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015
Commented:
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

Author

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

Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015
Commented:
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.
Surranoapplication manager
Commented:
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

Author

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

application manager
Commented:
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

Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
btw arthrex - I wrote an article awhile back on SQL Server CASE solutions that will help you with a lot of these syntax questions.

Author

Commented:
Thank you very much!!

Author

Commented:
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!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial