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: 15986 # Views: 1 # Ratings: 0
Version: Unknown Category: General VFP Topics
Date: Sunday, October 06, 2002 3:54:06 AM         
   


> ACTUALLY THOSE TABLES ARE VIEWS(I MISTAKED) AND 1ST VIEW have all custid values.
> PLEASE TELL ME HOW I SOLVE MY PROBLEM
>
> > > 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


Oh. If Table1 has all custo-id values then :
SELECT table1.*, ;
            table2.phone, ;
            table2.po-box, ;
            table3.salary, ;
            table3.score, ;
            table4.quenty, ;
            table4.language, ;
            table5.post, ;
            table5.result ;
  FROM table1 LEFT OUTER JOIN table2 ;
      ON table1.custo-id = table2.custo-id ;
            LEFT OUTER JOIN table3 ;
      ON table1.custo-id = table3.custo-id ;
           LEFT OUTER JOIN table4 ;
      ON table1.custo-id = table4.custo-id ;
           LEFT OUTER JOIN table5 ;
     ON table1.custo-id = table5.custo-id ;
INTO MyResultCursor NOFILTER

This won't be a fast query if the tables are big. Note that you should use the NVL() function if you don't want .NULL. values for non matched fields.

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