🚀 go-pugleaf

Gordon,

In the future, please post table schemas as CREATE TABLEs and sample data as
INSERTs.

create table Tab1
(
 ID int NOT NULL primary key,
 Name char (2) NOT NULL,
 State char (2) NOT NULL
)

create table Tab3
(
 ID int NOT NULL primary key,
 GroupName char (7) NOT NULL
)
create table Tab2
(
 ID int NOT NULL primary key,
 Name varchar (5) NOT NULL,
 Tab1_FK int NOT NULL
   foreign key references Tab1 (ID),
 Tab3_FK int NOT NULL
   foreign key references Tab3 (ID)
)

go

insert into Tab1 values (1, 'AA', 'CA')
insert into Tab1 values (2, 'BB', 'CA')
insert into Tab1 values (3, 'CC', 'CA')
insert into Tab1 values (4, 'DD', 'NY')
insert into Tab1 values (5, 'EE', 'CA')

insert into Tab3 values (20, 'GROUP_X')
insert into Tab3 values (21, 'GROUP_Y')

insert into Tab2 values (10, 'One', 1, 20)
insert into Tab2 values (11, 'Two', 1, 21)
insert into Tab2 values (12, 'Three', 3, 20)
insert into Tab2 values (13, 'Four', 5, 20)
insert into Tab2 values (14, 'Five', 5, 21)
insert into Tab2 values (15, 'Six', 4, 20)

select t1.ID, t1.Name,
            x.ID, x.Name, y.ID, y.Name,
            t1.State
from Tab1 as t1
left outer join (select t2.Tab1_FK, t2.ID, t2.Name
                          from Tab2 as t2
                          join Tab3 as t3_20
                             on (t2.Tab3_FK = 20 and
                                    t3_20.ID = 20)) as x
  on (t1.ID = x.Tab1_FK)
left outer join (select t2.Tab1_FK, t2.ID, t2.Name
                          from Tab2 as t2
                          join Tab3 as t3_21
                            on (t2.Tab3_FK = 21 and
                                   t3_21.ID = 21)) as y
  on (t1.ID = y.Tab1_FK)
where t1.State = 'CA'
order by t1.ID

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.

"ProDevelop" <ProDevelop@gmx.net> wrote in message
news:a7qn6c$bq0$07$1@news.t-online.com...
> Hi Group,
>  does anybody know, if it is possible to build a Select Statement for
> following problem ?
>
> Scenario given:
>
> Tab1
> ID          Name            State
> 1            AA                CA
> 2            BB                CA
> 3            CC                CA
> 4            DD                NY
> 5            EE                 CA
>
>
> Tab2
> ID          Name           Tab1.FK                    Tab3.FK
> 10          One              1                                 20
> 11          Two              1                                 21
> 12          Three            3                                 20
> 13          Four              5                                 20
> 14          Five               5                                 21
> 15          Six                4                                 20
>
>
>  Tab3
> ID          GroupName
> 20          GROUP_X
> 21          GROUP_Y
>
>
>  Result List should show only Rows from Tab1 with State=CA and
> using Tab3 Entries dynamic as follow:
>
> ID NAME   GROUP_X    GROUP_Y    STATE
> ------------------------------------------------------
> 1  AA          10 One          11 Two          CA
> 2  BB           (empty)          (empty)          CA
> 3  CC          12 Three        (empty)          CA
> 5  EE           13 Four         14 Five           CA
> aso.
>
>  Any idea ?
>
>  thx. in advance. Gordon.
>
>
>
>