Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 434
  • Last Modified:

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

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
arthrex
Asked:
arthrex
  • 6
  • 5
  • 2
4 Solutions
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
 
arthrexAuthor 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!
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
arthrexAuthor 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
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
 
arthrexAuthor 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

0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
 
SurranoCommented:
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
 
arthrexAuthor 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

0
 
SurranoCommented:
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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
 
arthrexAuthor Commented:
Thank you very much!!
0
 
arthrexAuthor 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!
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 6
  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now