Selecting a specific value from multiple unique rows when a single value is different

Question

So i have a small amount of SQL below.

SELECT DISTINCT
Id,
Date, 
Name_Detail,

(SELECT CASE 
        WHEN (Name_Detail IN ('a1','a2') and Name_Detail IN ('d1','d2')) 
        THEN 'AUX' 
        ELSE
            (CASE 
            WHEN Name_Detail IN ('d1','d2') 
            THEN 'DIN' 
            ELSE
                (CASE 
                WHEN Name_Detail IN     ('a1','a2') 
                THEN 'AUX' 
                ELSE 'NO' 
                END) 
            END) 
        END)
AS [Alias]

This gives the following results.

Id  Date       Name_Detail    Alias
1   01/04/2013      d1         Din
1   01/04/2013      d2         Din
2   02/09/2013      a1         Aux
2   02/09/2013      a2         Aux
2   02/09/2013      d1         Din
2   02/09/2013      d2         Din

If i remove the name_detail part it then gives the below.

Id  Date            Alias
1   01/04/2013       Din
2   02/09/2013       Aux
2   02/09/2013       Din

This still however gives a duplicate line fore the AUX and the DIN for ID 2.

What i want it to follow is...

If the Name_Detail contain(s) a1 and(or) a2 its Alias is AUX.

If the Name_Detail contain(s) d1 and(or) d2 its Alias is DIN.

If the Name_Detail contain(s) a1 and(or) a2 AND d1 and(or) d2 its Alias is AUX.

So for the above data should result in 2 lines as id 1 has d1 and d2 so it shows the Alias as DIN and id 2 has d1,d2,a1,a2 so it shows the Alias as AUX.

Id  Date            Alias
1   01/04/2013       Din
2   02/09/2013       Aux

It's not clear what is the rule to select DATA field? Or the same ID has the same data? — valex
– valex, Commented Nov 19, 2013 at 11:52
There are 4 lines returned witht he ID of 2, i want there only to be 1 line returned by following the rules below. If the Name_Detail contain(s) a1 and(or) a2 its Alias is AUX. If the Name_Detail contain(s) d1 and(or) d2 its Alias is DIN. If the Name_Detail contain(s) a1 and(or) a2 AND d1 and(or) d2 its Alias is AUX. — Matt
– Matt, Commented Nov 19, 2013 at 12:06

valex · Accepted Answer · 2013-11-19 12:24:45Z

For these exact conditions with fixed values (a1,a2,d1,d2) here is a query:

SQLFiddle demo

select ID,
       max(DATE),
       CASE WHEN MIN(Name_Detail)in('a1','a2')
                 and 
                 MAX(Name_Detail)in('d1','d2')
                 THEN 'AUX'
            WHEN MIN(Name_Detail)in('d1','d2')
                 THEN 'DIN'
            WHEN MAX(Name_Detail)in('a1','a2')
                  THEN 'AUX'
            ELSE 'NO'
        END as Alias
FROM T
GROUP BY ID

In general you should use subquery to check all records with the same ID through the table. In your case the first conditions is newer TRUE because you check only ONE current record.

SQLFiddle demo

select DISTINCT ID,
       DATE,


          CASE WHEN EXISTS(SELECT ID FROM T 
                               WHERE ID=T1.ID
                                     AND   
                                     Name_Detail in('a1','a2'))
                 and 
                 EXISTS(SELECT ID FROM T 
                               WHERE ID=T1.ID
                                     AND   
                                     Name_Detail in('d1','d2'))
                 THEN 'AUX'
            WHEN EXISTS(SELECT ID FROM T 
                               WHERE ID=T1.ID
                                     AND   
                                     Name_Detail in('d1','d2'))
                 THEN 'DIN'
            WHEN EXISTS(SELECT ID FROM T 
                               WHERE ID=T1.ID
                                     AND   
                                     Name_Detail in('a1','a2'))
                  THEN 'AUX'
            ELSE 'NO'
        END as Alias
FROM T as T1

user2783484 · Accepted Answer · 2013-11-19 11:48:04Z

1

I am not sure what you are trying to do, your third rule violates first and second rule, here i have the modified query.

SELECT DISTINCT
Id,
Date, 
Name_Detail,

(SELECT CASE 
            WHEN (Name_Detail IN ('a1','a2') and Name_Detail IN ('d1','d2')) 
        THEN 'AUX' 
        ELSE
            (CASE 
                WHEN Name_Detail IN ('d1','d2') 
                THEN 'DIN' 
                ELSE
                    (CASE 
                        WHEN Name_Detail IN ('a1','a2') and Name_Detail IN ('d1','d2')
                        THEN 'AUX' 
                        ELSE 'NO' 
                        END) 
            END) 
        END)
AS [Alias]

answered Nov 19, 2013 at 11:48

user2783484

9691 gold badge6 silver badges10 bronze badges

1 Comment

Matt Over a year ago

The first rule is if the name detail has a1 and(or) a2 then show AUX The second rule is if the name detail has d1 and(or) d2 then show DIN The third rule is if the name detail has ((a1 and(or) a2) AND (d1 and(or) d2))

Govind · Accepted Answer · 2013-11-19 12:09:33Z

1

If you are using distinct on fields that it is considering all column as distinct so it is giving result like that

answered Nov 19, 2013 at 12:09

Govind

151 silver badge10 bronze badges

Collectives™ on Stack Overflow

Selecting a specific value from multiple unique rows when a single value is different

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related