Lee Lee - 4 months ago 9
SQL Question

How to use the values of one column from the select statement to display a column from a join?

This is my current

SELECT
statement:

SELECT DISTINCT [QVD/CSV Names] AS 'CSV/Excel Files',
[SourceTypeID],
CASE
WHEN substring([QVD/CSV Names], len([QVD/CSV Names]) - 3, 4) = '.csv' THEN 'CSV'
WHEN substring([QVD/CSV Names], len([QVD/CSV Names]) - 3, 4) = '.xls' THEN 'Excel'
WHEN substring([QVD/CSV Names], len([QVD/CSV Names]) - 4, 5) = '.xlsx' THEN 'Excel'
END
AS [Source Types]
FROM STG_QVDs
LEFT OUTER JOIN SourceTypes AS c
ON [Source Types] = c.[SourceTypeName]
WHERE [QVD/CSV Names] IS NOT NULL
AND substring([QVD/CSV Names], len([QVD/CSV Names]) - 3, 4) <> '.qvd'


Right now, I have three columns. One column would display the name of the files,
CSV/Excel Files
. Another is suppose to display the ID of the source (which I am generating on the spot in another column using the case statement). Since the column isn't in the database, I had to create it there and this is where I'm stuck. I do not want to display the
[Source Types]
column. I just want
CSV/Excel Files
and
SourceTypeID
. Is there a way for me to be able to display the appropriate ID for the rows using that generated column?

This is how it looks in the SourceTypes lookup table.

SourceTypeID SourceTypeName
------------ --------------
1 CSV
2 Excel


This is what it displays:

CSV/Excel Files Source Types
--------------- ------------
file1.csv CSV
file2.xls Excel


I want it to display:

CSV/Excel Files SourceTypeID
--------------- ------------
file1.csv 1
file2.xls 2

Answer

You can prepare CSV / Excel values first in a CTE (Common Table Expression) and then join them to lookup table. Something like this.

;with names as ( -- start CTE
SELECT DISTINCT [QVD/CSV Names] AS [CSV/Excel Files],
                CASE --"ends with"
                    WHEN [QVD/CSV Names] like '%.csv'  THEN 'CSV'
                    WHEN [QVD/CSV Names] like '%.xls'  THEN 'Excel'
                    WHEN [QVD/CSV Names] like '%.xlsx' THEN 'Excel'
                END
                AS [Source Types]
FROM STG_QVDs
WHERE [QVD/CSV Names] IS NOT NULL
    AND [QVD/CSV Names] not like '%.qvd'
) --end CTE
select names.[CSV/Excel Files], c.[SourceTypeID]
from names
LEFT OUTER JOIN SourceTypes AS c
    ON  names.[Source Types] = c.[SourceTypeName]