Improve company productivity with a Business Account.Sign Up

x
?
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
Medium Priority
?
473 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
13 Comments
 
LVL 66

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 66

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
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

 

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 66

Assisted Solution

by:Jim Horn
Jim Horn earned 1200 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
 
LVL 66

Assisted Solution

by:Jim Horn
Jim Horn earned 1200 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 800 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 800 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 66

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Article by: Tammy
MySQLTuner is a script written in Perl that allows you to review a MySQL installation quickly and make adjustments to increase performance and stability. The current configuration variables and status data is retrieved and presented in a brief forma…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

595 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