union ?

20 Jan 2006 8:35 PM

Fred

hi, i have tables Customer, CustomerGroup and CustomerCustomerGroupXref
( :) ), now i want to find customers having say vip_flag, only that flag
can be set for whole gropus and the indivdual customers inside group
wont than have it set

curenty i do

select id, code from customers
where vip_flag = 1

union

select ... from customers join CustomerGroup
.... where CustomerGroup.vip_flag = 1

but there must be a better way ...

TIA

20 Jan 2006 8:43 PM

Jens

If it isn´t possible to set the flag for individual customers, you can
only query for the CustomerGroups, right ? If there is something like a
Xref table the query should be something like this:

Select <columnlist>
FROM
Customer C
INNER JOIN CustomerCustomerGroupXref CX
ON C.CustomerId = CX.CustomerId
INNER JOIN CustomerGroup CG
ON CG.GroupId = CX.GroupId
Where CG.vip_flag = 1

Am I right ?

HTH, Jens Suessmeyer.

20 Jan 2006 8:50 PM

Jim Underwood

I see nothing at all wrong with your original approach, but if you want an
alternative, how about using exists?

select id, code from customers
where vip_flag = 1
or exists (select ... from customers join CustomerGroup
              ... where CustomerGroup.vip_flag = 1
                  AND CustomerGroup.CustomerID = Customer.CustomerID
              )

Show quote

"Fred" <fred@ilovespam.com> wrote in message
news:%23RVADEgHGHA.2036@TK2MSFTNGP14.phx.gbl...
> hi, i have tables Customer, CustomerGroup and CustomerCustomerGroupXref
> ( :) ), now i want to find customers having say vip_flag, only that flag
> can be set for whole gropus and the indivdual customers inside group
> wont than have it set
>
> curenty i do
>
> select id, code from customers
> where vip_flag = 1
>
> union
>
> select ... from customers join CustomerGroup
> ... where CustomerGroup.vip_flag = 1
>
> but there must be a better way ...
>
> TIA