Help for select statement

[robertoroberto]

Starting from this table structure:

K1  K2  K3  K4  K5  V1      V2 
----------------------------------- 
        AA  BB  C1  D1      D2 
        AA  KK  C1  D1      D2 
01      AA  KK  C1  D101    D201 
01  GR  AA  KK  C1  D101GR  D201GR 
        AA  KK  C2  D1      D2 
01      AA  KK  C2  D101    D2 
    US  AA  KK  C2  D1US    D2 
01  US  AA  KK  C2  D101US  D2 
02      AA  KK  C2  D102    D2 
    GR  AA  KK  C2  D1GR    D2 
02  GR  AA  KK  C2  D102GR  D2 
01  GR  AA  KK  C2  D101GR  D2
01      AA  KK  C4  D101    D201 
        AA  KK  C5  D1      D2

where K1, K2, K3, K4 and K5 are the unique primary key of the table


I want retrieve all row where:
K3 = AA
K4 = KK
and for K1=01 and K2=US

K2 is a "logical" reference to another table User/Group

USER    GROUP 
____________________ 
US        GR
USER2   GROUP2 
USER3   GROUP

So in this case 4 rows must be returned: one for K5 = C1 and another
one for K5=C2
I don't know how many rows will be returned because i don't know how
many K5 value will be for K1-K4 keys

Wished Results table

K1  K2  K3  K4  K5  V1      V2 
----------------------------------- 
01  GR  AA  KK  C1  D101GR  D201GR 
01  US  AA  KK  C2  D101US  D2 
01      AA  KK  C4  D101    D201 
        AA  KK  C5  D1      D2

What kind of "crazy" SQL i must do to have it?

Thank You !
Roberto

[rmiao]

On which rdbms?

[skhanal]

It's not clear what you are trying to get. When I look at the data with these filters

K3 = AA
K4 = KK
and for K1=01 and K2=US

I only see one row matching all criteria

01 US AA KK C2 D101US D2

[robertoroberto]

I want return all rows that match my criteria following this roles:

Only rows with K3 = AA and K4 = KK (and this is simple)
But K1 and K2 can be interpreted as Company Code and User.
So i can have rows valid for all company (void field) or for a specify companycode.
The same for user (K2): in this case i can have row valid for all users (void field) or for a group of users (i user can be a member only of one group) or for a specify user.

Following this rules i must return only one record for each K5 value that match be best detail.

So if i have for the same K3, k4 and k5 value 2 rows for the same company (K1), one for User (k2) = "USER1" and another for Group (K2) = "GROUP1" and USER1 is a member of GROUP1, i must return only the records for that user (and not also for that group)
Ty