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