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!
BlakeMcKennaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornSQL Server Data DudeCommented:
> 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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BlakeMcKennaAuthor Commented:
That's what I was looking for! Thanks Jim...
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

Exploring SharePoint 2016

Explore SharePoint 2016, the web-based, collaborative platform that integrates with Microsoft Office to provide intranets, secure document management, and collaboration so you can develop your online and offline capabilities.

BlakeMcKennaAuthor Commented:
Shaun,

Using your statement, what happens if none of the "stationName" columns are NULL?
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.
BlakeMcKennaAuthor Commented:
I'm running SQL Server 2008 and apparently the COALESE statement isn't available in this version.
Jim HornSQL Server Data DudeCommented:
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.
BlakeMcKennaAuthor Commented:
Will do...thanks!
Shaun KlineLead Software EngineerCommented:
The COALESCE function exists, but the code I provided contained a misspelling. It did not have the second C.
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!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.