Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Jamie Osborn
  Where is Jamie Osborn?
 Melbourne
 Australia
 Jamie Osborn
 To: munir hossain
  Where is munir hossain?
 dhaka
 Bangladesh
 munir hossain
 Tags
Subject: RE: Join few table with every fields and every ...
Thread ID: 15955 Message ID: 15960 # Views: 1 # Ratings: 0
Version: Unknown Category: General VFP Topics
Date: Saturday, October 05, 2002 5:56:24 AM         
   


> Hello again
> I got a new prob now. I have 5 table and every table has 3 fields. Every table has One common field.
> Now i want to join those table with every fields and every records without repetation of the common field. Like
>
>
> Table-1
> custo-id Name Address
>
> Table-2
> custo-id Phone po-box
>
> Table-3
> custo-id salary score
>
>
> Table-4
> custo-id Quenty Language
>
> Table-5
> custo-id post result
>
>
> Now i want a result in a new table with every fields and every records(match and unmatch records) like this
>
>
> New_Table
>
> custo-id Name Address Phone po-box salary score Quenty Language post result
>

You need to do FULL OUTER JOINS.

I asuume from what you have said that there is no 1 table that is guaranteed to have all custid values. Therefore you will need to do this :
SELECT table1.*, ;
            table2.phone, ;
            table2.po-box ;
  FROM table1 FULL OUTER JOIN table2 ;
     ON table1.custo-id = table2.custo-id ;
INTO CURSOR step1 NOFILTER

INDEX ON custo-id TAG custo-id && Speed up next join

SELECT step1.*, ;
            table3.salary, ;
            table3.score ;
  FROM step1 FULL OUTER JOIN table3 ;
      ON step1.custo-id = table3.custo-id ;
INTO CURSOR step2 NOFILTER

INDEX ON ..... (and so on)


Cheers,
Jamie
jamie.osborn@foxite.com

ENTIRE THREAD

Join few table with every fields and every records Posted by munir hossain @ 10/4/2002 10:51:48 PM
RE: Join few table with every fields and every ... Posted by Jamie Osborn @ 10/5/2002 5:56:24 AM
RE: Join few table with every fields and every ... Posted by munir hossain @ 10/5/2002 9:25:11 AM
RE: Join few table with every fields and every ... Posted by Jamie Osborn @ 10/6/2002 3:54:06 AM