Improve company productivity with a Business Account.Sign Up

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

Constructing a SELECT statement using a CASE statement?

I have a column that exists in 3 different tables. Each table could potentially have a value in it at the same time. I just need to return that value from the first occurrence of whichever table contains a value. Here is what I have so far.

SELECT	A.serialNO AS 'S/N'
		,A.main_ID AS 'Test ID'
		,ISNULL(B.dateCreated,D.dateCreated) AS 'Calibration Date'
	        ,CASE WHEN B.station_ID > 0 THEN F.stationName ELSE '' END
		 + CASE WHEN D.station_ID > 0 THEN G.stationName ELSE '' END
		 + CASE WHEN E.station_ID > 0 THEN H.stationName ELSE '' END AS 'Test Station'
		,B.[load] AS 'Capacity'
		,B.endTime AS 'Ending Time'
  FROM  HEADER_Item A LEFT JOIN TEST_HEADER_Load B ON A.main_ID = B.main_ID
		LEFT JOIN TEST_HEADER_Creep D ON A.main_ID = D.main_ID
		LEFT JOIN TEST_HEADER_Repeatability E ON A.main_ID = E.main_ID
		LEFT JOIN LKUP_TestStations F ON B.station_ID = F.station_ID
		LEFT JOIN LKUP_TestStations G ON D.station_ID = G.station_ID
		LEFT JOIN LKUP_TestStations H ON E.station_ID = H.station_ID
 WHERE	A.serialNO = @serialNO

Open in new window


The table "LKUP_TestStations" contains the following columns: "station_ID", "stationName". The Primary Key is the "station_ID" in this table.

The "station_ID" column resides as a Foreign Key in the tables  "TEST_HEADER_Load", "TEST_HEADER_Creep" and "TEST_HEADER_Repeatability".

So, with the statement above, it is returning 3 values from the CASE statement when I only need one. How can I get it to just return the first occurrence of "station_ID".

I hope I've made sense with this.

Thanks!
0
BlakeMcKenna
Asked:
BlakeMcKenna
  • 5
  • 3
  • 2
1 Solution
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
> I just need to return that value from the first occurrence of whichever table contains a value.

Not entirely getting the table logic here, but if the question is return the first non-zero value between the numeric columns b.StationID, c.StationID, and d.StationID, then something like.
SELECT blah, blah, blah, 
  CASE
    WHEN b.StationID > 0 THEN b.StationId
    WHEN d.StationID > 0 THEN d.StationId
    WHEN e.StationID > 0 THEN e.StationID
    ELSE '' END as StationID
FROM ...

Open in new window

btw I have an article out there on SQL Server CASE Solutions, with a wompload of examples, although it doesn't mention this scenario.
0
 
BlakeMcKennaAuthor Commented:
That's what I was looking for! Thanks Jim...
0
 
Shaun KlineLead Software EngineerCommented:
Because you are using left joins, that raises the possibility that a null value could be returned for any of the stationName  fields used in your CASE statement. You could replace the entire CASE statement with the COALESCE function:
COALESE(F.stationName, G.stationName, H.stationName)

Open in new window

0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
BlakeMcKennaAuthor Commented:
Shaun,

Using your statement, what happens if none of the "stationName" columns are NULL?
0
 
Shaun KlineLead Software EngineerCommented:
The COALESCE function returns the first non-null value in the list. If all three values could be null and you do not want null returned, add a fourth value that you would treat as the default value to return, such as an empty string.
0
 
BlakeMcKennaAuthor Commented:
I'm running SQL Server 2008 and apparently the COALESE statement isn't available in this version.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Blake - Yep.  When asking T-SQL questions please tell us the version of SQL you're running, as 2012 has a lot of improvements over 2008/R2 that can make a big difference in the answers we provide.
0
 
BlakeMcKennaAuthor Commented:
Will do...thanks!
0
 
Shaun KlineLead Software EngineerCommented:
The COALESCE function exists, but the code I provided contained a misspelling. It did not have the second C.
0
 
BlakeMcKennaAuthor Commented:
Shaun,

That worked. Funny though, the intelisense didn't prompt me when I started typing in the word. It only showed up as a reserved word when I finished typing the whole word. But it worked!

Thanks!
0
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

Featured Post

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.

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