Compare query result on the basis of single column

Question

Example:

select c_id,'VIP Customer' [Customer Level] from customer where ...

union

select c_id,'Regular Customer' [Customer Level] from customer where ...

if c_id is part of first level (VIP) can't be 2nd level(Regular). I am using Union it's not working because second column data is different.

Can someone suggest?

yes.. I am segmenting customers on basis of some criteria....My resultset for each of level is ready.. Now I am doing check if some c_id is part of first level (VIP) can't be 2nd level(Regular) or some other level below it. — user5342687, Sep 16 '15 at 14:21

score 0 · Answer 1 · edited May 23 '17 at 11:51

0

I'm not sure exactly which SQL variant you are using but you may find it helpful to look at the 'case' expression rather than a union, see:

How do I perform an IF...THEN in an SQL SELECT?

edited May 23 '17 at 11:51

Community

1
1

answered Sep 16 '15 at 14:23

Damien Allison

109
4

score 0 · Answer 2 · answered Sep 16 '15 at 14:25

You could check, if the result-id was included in the first query. So something like this:

SELECT c_id,'VIP Customer' [Customer Level] from customer where [CONDITION]
UNION
(select c_id,'Regular Customer' [Customer Level]
from customer where [SECOND_CONDITION] AND
c_id not in 
(SELECT c_id,'VIP Customer' [Customer Level] from customer where [CONDITION]))

score 0 · Answer 3 · answered Sep 16 '15 at 14:27

So you're getting like ...

1, 'VIP'

1, 'Regular'

?

If so, I'm not sure if the where clause is 100% done yet. If it finds the same customer to be multiple types, that might be a red flag. If the customer is saved as multiple types, then a case statement is probably the way to go in this situation instead of a union.

select c_id, case when... then 'VIP' when... then 'Regular' end

Mattia Caputo · Accepted Answer · 2015-09-16T14:37:56.047

this is the error Client query.

select A.RC_id
from (
      select c_id RC_id
      from customer
      where 'Regular Customer' criteria) A
inner join (
      select c_id VIP_id
      from customer
      where 'VIP Customer' criteria) B
on A.RC_id=B.VIP_id

or you can apply both criteria on the Client, i think is the shortest solution

select c_id
from customer
where ('VIP Customer criteria' AND 'Regular Customer criteria')

score 0 · Answer 5 · answered Sep 16 '15 at 14:37

CASE seems to be a nice solution here, you can add more Customer Levels if needed also.

select c_id, [Customer Level] = 
    CASE
        WHEN ... THEN  'VIP Customer'
        ELSE 'Regular Customer'
    END

 from customer

More about CASE on MSDN: https://msdn.microsoft.com/en-us/library/ms181765.aspx

Compare query result on the basis of single column

5 Answers5