Set the value in a column within a SELECT query-CodePudding

I have two (simplified) tables as follows:

Table AAPT

Well	Type	Comment
A	PT	Good
B	PT-S	Average

Table AASPIN

Well	Type	Remark
A	PT	Bad
B	PT-S	Fair

I want to run a SELECT query that results in the following:

Well	Type	Comment	Remark	PTType	SpinType
A	SPINPT	Good	Bad	PT	SPIN
B	SPINPT	Average	Fair	PT-S	SPIN-PT

I use both MS Access and SQL-Server.

In Access, I use the following query:

SELECT A.Well, A.Type, A.Comment, B.Remark, A.Type AS PTType, B.Type AS SpinType
FROM AAPT A, AASPIN B
WHERE A.Well = B.Well

However, I do not know how to set the value of the Type column to "SPINPT"

Any suggestions?

CodePudding user response：

Using a modern explicit join and a static value for Type:

SELECT
    A.Well
    , 'SPINPT' AS [Type]
    , A.Comment
    , B.Remark
    , A.[Type] AS PTType
    , B.[Type] AS SpinType
FROM AAPT A
INNER JOIN AASPIN B ON A.Well = B.Well;