CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Ephedra
Ephedra

Reputation: 881

WHERE Clause does not accept just defined column

This Code does not work: 

SELECT 
( 
    SELECT [T_Licence].[isInstalled]    
    FROM [T_Licence]    
    WHERE [T_Licence].[System]  = [T_System].[ID]   
    AND [T_Licence].[Software] = 750
) AS [IsInstalled] ,*
FROM [T_System]
WHERE [IsInstalled] = 1

I have to do it this way, but this makes the whole code so complicated. I really dont want that:

SELECT 
( 
    SELECT [T_Licence].[isInstalled]    
    FROM [wf_subj_all].[T_Licence]  
    WHERE [T_Licence].[System]  = [T_System].[ID]   
    AND [T_Licence].[Software] = 750
) AS [IsInstalled] ,*
FROM [wf_subj_it].[T_System]
WHERE 
(
    SELECT 
    ( 
        SELECT [T_Licence].[isInstalled]    
        FROM [wf_subj_all].[T_Licence]  
        WHERE [T_Licence].[System]  = [T_System].[ID]   
        AND [T_Licence].[Software] = 750
    )
) = 1

Is there any way to do it like shown in the first code snippet? So that the code stays somehow readeble.

thx very much

Upvotes: 1

Views: 60

Answers (3)

Henk Kok
Henk Kok

Reputation: 383

The assumptions I made for my answer:

  • You're trying to select the systems (table T_System) which have software with id=750 installed
  • The table T_License contains the installed information
  • There is a 1:n relation between T_System and T_License: T_License may contain 0, 1 or more records per Sytem value...
  • but the combination System plus Software is unique

I think this will work

SELECT l.[isInstalled], s.*
FROM   [wf_subj_it].[T_System] AS s
       INNER JOIN [wf_subj_it].[T_License] AS l
       ON  l.[System] = s.[ID]
       AND l.[Software] = 750
       AND l.isInstalled = 1

Upvotes: 0

Magnus
Magnus

Reputation: 46997

Just wrap the query with an outer select and it should work.

SELECT *
FROM
(
  SELECT 
  ( 
    SELECT [T_Licence].[isInstalled]    
    FROM [T_Licence]    
    WHERE [T_Licence].[System]  = [T_System].[ID]   
    AND [T_Licence].[Software] = 750
  ) AS [IsInstalled], *
  FROM [T_System]
) As tbl1
WHERE [IsInstalled] = 1

Upvotes: 2

Devart
Devart

Reputation: 122032

Try this one -

SELECT *
FROM wf_subj_it.T_System s
CROSS APPLY (
    SELECT /*TOP(1)*/ t.isInstalled   
    FROM wf_subj_all.T_Licence t
    WHERE t.[System]  = s.ID   
    AND t.Software = 750
) t
WHERE t.isInstalled = 1

Upvotes: 2

Related Questions