Select groups. Case ForRun ?

Question

here is a structure :-/ So I need to select ID and Names from CfgListGroupParIzm for CfgIzmeritel using Type and where ForRun - False it's

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

AND ID and NamePoint(from CfgIzmerPoint)+Name from CfgListGroupParIzm from same CfgIzmeritel where ID_Izmerit from CfgIzmeritel =ID_Izmerit from CfgIzmerPoint and ForRun from CfgListGroupParIzm - True

so finally I need something like

SELECT A.ID_ListGroupParIzm, A.Name
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
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

I think I need use some other construction without Union because I make mostly the same in both selects :-/

Answer 1

You could use a UNION ALL

SELECT A.ID_ListGroupParIzm, A.Name 
FROM    CfgListGroupParIzm A, 
        CfgIzmeritel B  
WHERE   A.ID_TypeIzmerit = B.ID_TypeIzmerit  
AND     B.ID_Izmerit=@ID_Izmerit 
AND     A.ForRun=0 
UNION ALL
SELECT  A.ID_ListGroupParIzm, 
        (C.Name + A.Name) AS Name 
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 

UNION ALL will bring back all the results, from both queries. If you require the distinct list of this, without any duplicates, use UNION.

Have a look at Union vs. Union All

and SQL UNION Operator

Please also remember that using aliases other than A,B,C,etc is more appropriate. Try using slightly more descriptive aliases that will make it easier to follow in larger queries.

If my assumption is correct that CfgIzmeritel and CfgIzmerPoint are one to one you could try this using

SELECT  grp.ID_ListGroupParIzm,
    CASE 
        WHEN ForRun=1 AND pnt.ID_TypeIzmerit IS NOT NULL THEN (C.Name + A.Name) 
        ELSE grp.Name
    END AS Name
FROM    CfgListGroupParIzm grp INNER JOIN
        CfgIzmeritel item ON grp.ID_TypeIzmerit = item.ID_TypeIzmerit LEFT JOIN
        CfgIzmerPoint pnt ON grp.ID_TypeIzmerit = pnt.ID_TypeIzmerit
WHERE   item.ID_Izmerit=@ID_Izmerit

From the where clause you should be able to ignore the ForRun, as this will only be used in the CASE statement.

I stile think that for readibility you should use the UNION/UNION ALL and only try to create a single query if the performance is bad (after you had a look at indexes).