CODEWITHSUNDEEP
sql-servert-sqlselectwhere-clause
Filippo Vigani
Filippo Vigani

Reputation: 1004

SQL WHERE statements in SELECT statements

I'm making a query which takes data from the rows of a table and puts it under the same column:

SELECT [t].*,
       [d].[Description] AS [Name],
FROM   [TrainerClass] AS [t] join [DescriptionTranslation] AS [d] on [t].[Code] = [d].[Code]
WHERE  [d].[TableName] = 'TrainerClass' AND 
       [d].[FieldName] = 'Name' AND 
       [d].[Language] = 'en-EN';

This works just fine, but it returns a single column. I want to get the same result with more columns, but I can't figure out how to do it with the WHERE statements. I'd like to achieve something like:

SELECT [t].*,
       [d].[Description] AS [Name] WHERE [d].[FieldName] = 'Name',
       [d].[Description] AS [Info] WHERE [d].[FieldName] = 'Info',
FROM   [TrainerClass] AS [t] join [DescriptionTranslation] AS [d] on [t].[Code] = [d].[Code]
WHERE  [d].[TableName] = 'TrainerClass' AND 
       [d].[Language] = 'en-EN';

I know it's not a proper syntax, and that's why I'm asking you how to get this result. Thank you in advance :)

EDIT:

What I don't want:

No

What I want:

Yes

(Examples made with only one record, there should be more)

Upvotes: 2

Views: 154

Answers (4)

Aman Prajapati
Aman Prajapati

Reputation: 157

Use below CROSS APPLY for your query

SELECT  [t].*,
        [DTNameDescription].[Description] AS [Name]
        [DTInfoDescription].[Description] AS [Info] 
FROM    [TrainerClass] AS [t] 
        CROSS APPLY (
            SELECT  [Description] 
            FROM    [DescriptionTranslation] AS [d] 
            WHERE   [t].[Code] = [d].[Code] 
                        AND [d].[FieldName] = 'Name'
        ) AS DTNameDescription
        CROSS APPLY (
            SELECT  [Description] 
            FROM    [DescriptionTranslation] AS [d] 
            WHERE   [t].[Code] = [d].[Code] 
                        AND [d].[FieldName] = 'Info'
        ) AS DTInfoDescription
WHERE   [d].[TableName] = 'TrainerClass' AND 
        [d].[Language] = 'en-EN';

Upvotes: 0

Kaf
Kaf

Reputation: 33839

You can use a CASE statement like below (I think that is what you are looking for). Mind you, mismatching rows in each case will get null values or you can add the else part to the case statement.

SELECT [t].*,
       CASE WHEN [d].[FieldName] = 'Name' THEN [d].[Description] END AS [Name],
       CASE WHEN [d].[FieldName] = 'Info' THEN [d].[Description] END AS [Info]

FROM   [TrainerClass] AS [t] join [DescriptionTranslation] AS [d] 
                             on [t].[Code] = [d].[Code]
WHERE  [d].[TableName] = 'TrainerClass' AND  --[d].[FieldName] = 'Name' AND 
       [d].[Language] = 'en-EN';

UPDATE:

As per your update, to get expected results from shown picture table, you can use Max() function with Group by:

;With cte as 
(
    --your first query goes here 
)
Select Id,Code, Max(Name) Name, Max(info) Info
from cte
Group by Id, Code

Of you can do without CTE as:

SELECT t.Id, t.Code,
       Max(CASE WHEN d.FieldName = 'Name' THEN d.Description END) AS Name,
       Max(CASE WHEN d.FieldName = 'Info' THEN d.Description END) AS Info
FROM   [TrainerClass] AS t join [DescriptionTranslation] AS d  on t.Code = d.Code
WHERE  d.TableName = 'TrainerClass' AND  --[d].[FieldName] = 'Name' AND 
       d.Language = 'en-EN'
GROUP BY t.Id, t.Code;

Upvotes: 4

Edward Leong
Edward Leong

Reputation: 47

Another way you can use subqueries for your query and it's quite similar to your intention. But only one caveat with this method, that is it can be quite slow if you have large record-sets. 

SELECT [t].*,
( SELECT [d].[Description]  FROM [DescriptionTranslation] [d] WHERE [d].[FieldName] = 'Name' and [t].[Code] = [d].[Code] [d].[TableName] = 'TrainerClass' AND 
[d].[Language] = 'en-EN') AS [Name], 
(SELECT [d].[Description]  FROM [DescriptionTranslation] [d] WHERE [d].[FieldName] = 'Info' and [t].[Code] = [d].[Code] [d].[TableName] = 'TrainerClass' AND 
[d].[Language] = 'en-EN') AS [Info]     
FROM   [TrainerClass] AS [t]

Upvotes: 1

Joe Enos
Joe Enos

Reputation: 40413

If I'm reading your request right, you'll need to join the table twice with different filters.

select t.*, dName.Description as [Name], dInfo.Description as [Info]
from TrainerClass t
join DescriptionTranslation dName
    on t.Code = dName.Code and dName.FieldName = 'Name'
join DescriptionTranslation dInfo
    on t.Code = dInfo.Code and dInfo.FieldName = 'Info'
where
    dName.TableName = 'TrainerClass' and dName.Language = 'en-EN'
    and dInfo.TableName = 'TrainerClass' and dInfo.Language = 'en-EN'

Upvotes: 1

Related Questions