Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Barak Ros
  Where is Barak Ros?
 TLV
 Israel
 Barak Ros
 To: Samir H.
  Where is Samir H.?
 Yogyakarta
 Indonesia
 Samir H.
 Tags
Subject: RE: relations
Thread ID: 310873 Message ID: 310875 # Views: 30 # Ratings: 0
Version: Visual FoxPro 6 Category: Databases, Tables and SQL Server
Date: Sunday, June 26, 2011 4:26:38 PM         
   


> > hi
> >
> > I need to set relation between a father table and 2 sons tables, so I can print a report that shows for each father row , the rows related to it in the 2 sons tables.
> >
> > for example:
> >
create cursor level1 (fid I,fval C(5))
> > create cursor level2a (fmyid I,fval C(5),fid I)
> > create cursor level2b (fmyid I,fval C(5),fid I)
> > 
> > insert into level1(fid,fval) values (1,'a')
> > insert into level1(fid,fval) values (2,'b')
> > insert into level1(fid,fval) values (3,'c')
> > **********************************************************
> > insert into level2a(fmyid,fval,fid) values (10,'aa',1)
> > insert into level2a(fmyid,fval,fid) values (20,'bb',1)
> > insert into level2a(fmyid,fval,fid) values (30,'cc',1)
> > 
> > insert into level2a(fmyid,fval,fid) values (40,'dd',2)
> > 
> > insert into level2a(fmyid,fval,fid) values (50,'ee',3)
> > insert into level2a(fmyid,fval,fid) values (60,'ff',3)
> > **********************************************************
> > insert into level2b(fmyid,fval,fid) values (100,'aaa',2)
> > insert into level2b(fmyid,fval,fid) values (200,'bbb',2)
> > insert into level2b(fmyid,fval,fid) values (300,'ccc',2)
> > 
> > insert into level2b(fmyid,fval,fid) values (400,'ddd',1)
> > 
> > insert into level2b(fmyid,fval,fid) values (500,'eee',3)
> > insert into level2b(fmyid,fval,fid) values (600,'fff',3)

> >
> > the report should display:
> > 1 a
> > from father table (level1)
> > and under it:
> > 10 aa 1
> > 20 bb 1
> > 30 cc 1
> > from son table 1 (level2a)
> > and under it:
> > 400 ddd 1
> > from son table 2 (level2b)
> >
> > then
> > 2 b
> > from father table (level1)
> > and under it:
> > 40 dd 2
> > from son table 1 (level2a)
> > and under it:
> > 100 aaa 2
> > 200 bbb 2
> > 300 ccc 2
> > from son table 2 (level2b)
> >
> > then
> > 3 c
> > from father table (level1)
> > and under it:
> > 50 ee 3
> > 60 ff 3
> > from son table 1 (level2a)
> > and under it:
> > 500 eee 3
> > 600 fff 3
> > from son table 2 (level2b)
> >
> > I tried to do the right
set relation
command but did not get the results I want
> > how can I do that?
> > TIA
> > BARAK
>
>
SELECT 000 as fmyid,l1.fval,l1.fid FROM level1 l1 ;
> UNION ALL ;
> SELECT l2a.* FROM level1 l1 ;
> JOIN level2a l2a ON l1.fid = l2a.fid ;
> UNION ALL ;
> SELECT l2b.* FROM level1 l1 ;
> JOIN level2b l2b ON l1.fid = l2b.fid ;
> order by 3,1
> 

> Reorder fields as necessary.
>
> Regards
> Samir

thanks, but this is not what I need. this is just an example of what I need , but the real tables has diffrent structures, level2a and level2b are diffrent.
Thanks any way
Barak

ENTIRE THREAD

relations Posted by Barak Ros @ 6/26/2011 3:24:06 PM
RE: relations Posted by Samir H. @ 6/26/2011 4:14:09 PM
RE: relations Posted by Barak Ros @ 6/26/2011 4:26:38 PM
RE: relations Posted by Samir H. @ 6/26/2011 4:32:56 PM
RE: relations Posted by Barak Ros @ 6/26/2011 4:38:23 PM
RE: relations Posted by Samir H. @ 6/26/2011 5:05:51 PM
RE: relations Posted by Tamar Granor @ 6/27/2011 10:51:16 PM