SQLTeam.com | Weblogs | Forums

Joining output from two tables

Transact-SQL
#1

I'm trying to join the output of two tables into one.

I have two tables with the following structure:

Table tb_LineStatus is:

LineNo, ParcelNo
1, 100
2, 0
3, 0

Table tb_SystemParamsSite is:

ParamName, ParamValue
ServerLine1, 1
ServerLine2, 1
ServerLine3, 0

My first select statement is (I'm only interested in Lines where the ParcelNo is 0):

SELECT [LineNo] FROM tb_LineStatus where ParcelNo = 0

This returns:

LineNo
2
3

My second statement is:

(SELECT row_number() OVER (ORDER BY ParamName) [LineNo],
CASE WHEN ParamValue = '1' THEN 'Live' ELSE 'Test' END AS Server
FROM tb_SystemParamsSite
WHERE ParamName like 'ServerLine%')

This returns:

LineNo, Server
1, Live
2, Live
3, Test

I'd like a Select statement that combines the two and returns the following:

LineNo, Server
2, Live
3, Test

Any help would be appreciated.

#2 

SELECT row_number() OVER (ORDER BY ParamName) [LineNo],
    CASE WHEN ParamValue = '1' THEN 'Live' ELSE 'Test' END AS Server
FROM tb_SystemParamsSite
WHERE ParamName like 'ServerLine%' AND
    [LineNo] IN (SELECT [LineNo] FROM tb_LineStatus where ParcelNo = 0)
#3

Hi,

Thanks for the reply but unfortunately that hasn't worked (I've marked in bold where I believe the error is). I get an error as LineNo isn't a column in the tb_SystemParamsSite table. The error message is:

"Invalid column name 'LineNo'."

SELECT row_number() OVER (ORDER BY ParamName) [LineNo],
CASE WHEN ParamValue = '1' THEN 'Live' ELSE 'Test' END AS Server
FROM tb_SystemParamsSite
WHERE ParamName like 'ServerLine%' AND
[LineNo] IN (SELECT [LineNo] FROM tb_LineStatus where ParcelNo = 0)

#4

Sorry, just ignore that, I allowed myself to get confused by the column names being exactly the same.

#5

With a CTE it's easily to understand:

WITH SystemParamsSite AS
(
	SELECT ROW_NUMBER() OVER (ORDER BY SPS.ParamName) AS [LineNo],
	CASE 
		WHEN SPS.ParamValue = '1' THEN 
			'Live' 
		ELSE 
			'Test' 
	END AS [Server]
	FROM tb_SystemParamsSite SPS
	WHERE SPS.ParamName LIKE 'ServerLine%' 
) 
SELECT [LineNo] 
FROM tb_LineStatus LS
	INNER JOIN SystemParamsSite SPS 
		ON LS.[LineNo]=SPS.[LineNo]
WHERE LS.ParcelNo = 0;
1 Like
#6

Hi,

As I wanted the Server status as well I had to change:

Select [LineNo]
to
Select LS.[LineNo], SPS.[Server]

Other than that, your reply was spot on. Thanks very much for the assistance.