exclude from union by only one out field ...

Question

SELECT A.ID_ListGroupParIzm, A.Name, 0 AS Point  
FROM CfgListGroupParIzm A, CfgIzmeritel B 
WHERE A.ID_TypeIzmerit = B.ID_TypeIzmerit   
AND B.ID_Izmerit=@ID_Izmerit AND A.ForRun=0 

UNION  

SELECT A.ID_ListGroupParIzm, (C.Name + ' ' + A.Name) AS Name, C.ID_IzmerPoint AS Point  
FROM CfgListGroupParIzm A, CfgIzmeritel B, CfgIzmerPoint C  
WHERE A.ID_TypeIzmerit = B.ID_TypeIzmerit  
AND B.ID_Izmerit=@ID_Izmerit AND A.ForRun=1  
AND C.ID_Izmerit=@ID_Izmerit

EXCEPT

SELECT ID_Group --, '' AS Name, 0 AS Point here I don't know their names and a Point :(
FROM TbUserGroup
WHERE ID_Izmerit=@ID_Izmerit AND
ID_User=@ID_User AND ID_Point=@ID_Point

there was idea - using Except but I need to know all fields for it (I know only one) , so I need exclude from this union nodes with ID_Group under except :-/ I'm thinking about combine it.

How to make it ? or some ideas ... or advices ...

Answer 1

You could try something like

SELECT  *
FROM    (
            SELECT  A.ID_ListGroupParIzm, 
                    A.Name, 
                    0 AS Point   
            FROM    CfgListGroupParIzm A, 
                    CfgIzmeritel B  
            WHERE   A.ID_TypeIzmerit = B.ID_TypeIzmerit    
            AND     B.ID_Izmerit=@ID_Izmerit 
            AND     A.ForRun=0  
            UNION   
            SELECT  A.ID_ListGroupParIzm, 
                    (C.Name + ' ' + A.Name) AS Name, 
                    C.ID_IzmerPoint AS Point   
            FROM    CfgListGroupParIzm A, 
                    CfgIzmeritel B, 
                    CfgIzmerPoint C   
            WHERE   A.ID_TypeIzmerit = B.ID_TypeIzmerit   
            AND     B.ID_Izmerit=@ID_Izmerit 
            AND     A.ForRun=1   
            AND     C.ID_Izmerit=@ID_Izmerit 
        ) sub 
WHERE   sub.ID_ListGroupParIzm NOT IN 
                (
                    SELECT  ID_Group 
                    FROM    TbUserGroup 
                    WHERE   ID_Izmerit=@ID_Izmerit 
                    AND     ID_User=@ID_User 
                    AND     ID_Point=@ID_Point 
                )