What is the best way to join multiple tables together?

[montypython1]

Greetings,

What is the best way to join multiple tables together?

I have 3 tables that I need to join into 1 table (or cursor). The 3 tables will be joined by a field which is present within all 3 tables called "index_ddd".

I know how to join 2 tables, and I know I can then join the combined table with the 3rd table, but I would like to learn a more elegant solution to handle this within one SQL.

Any suggestions.

Thanks,
Dave Higgins

 

[Olaf Doschke]

You write two join clauses each joining one table to all previously joined data, there is no clause joining two tables to the current one, only one after another.

Bye, Olaf.

 

[mmerlinn]

Did you try doing something like this?

[tt]SELECT * ;[/tt]

[tt] FROM table1, table2, table3 ;[/tt]
[tt] WHERE table1.index_ddd = table2.index_ddd ;[/tt]

[tt] AND table1.index_ddd = table3.index_ddd ;[/tt]​

[tt] INTO CURSOR combinedtables[/tt]​

​

That probably will not give you the results you need, but since you did not tell us the structure of your origin and destination tables, the code you are currently using, nor other details about your needs, it is impossible to point you to a solution that works for you if such a solution even exists.

mmerlinn

http://mmerlinn.com

Poor people do not hire employees. If you soak the rich, who are you going to work for?

"We've found by experience that people who are careless and sloppy writers are usually also careless and sloppy at thinking and coding. Answering questions for careless and sloppy thinkers is not rewarding." - Eric Raymond

 

[Mike Lewis]

Hi Dave,

Basically, you need to do something like this:

SELECT <fields from any or all of the tables> ;
  FROM Table1
  JOIN Table2 ON Table1.index_ddd = Table2.index_ddd
  JOIN Table3 ON Table1.index_ddd = Table3.index_ddd
  <other clauses, e.g WHERE>
  INTO CURSOR Results

In VFP8 and earlier, there was a limit of eight joins in a SELECT. There is no limit in VFP9.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

By the way, if you specify a list of tables in the FROM clause, like merlin, you're doing a cross join. The where clause is having the join conditions here, so you don't get the product of each and every record combination. Using joins is what I'd prefer, alone to differentiate between join conditions and filters.

If you think of this as more elegant, as it's shorter, just notice, you can only do inner joins this way, no outer or left joins.

Bye, Olaf.

 

[montypython1]

Hello Mike, Merlin and Olaf,

Thank you all for your suggestions. I used the JOIN command and it worked perfectly (and quickly).
As always, I appreciate your input.

Thanks,
Dave Higgins