17 Sep: Q&A;: How to retrieve related data from two or more tables

"Hi James, I know you do this everyday, but don't know how you merge different tables in your DB.

My users connect to my application through a DB, such as yours.

My DB tables are linked together with id, Now when a user is identified, I'd like to show him data from more than one table.

Is the best way to do this "merging tables ?"

Thanks again James"

Me:

Well, it's not merging, but to show info from two related tables, use this type of SQL statement:

DBGetRecords ( 'SELECT Table1.id, Table1.a, Table2.b FROM Table1 INNER JOIN Table2 ON Table1.id = Table2.id' );

This populates the recordset with the id and a columns from Table1 and the b column from Table2. It matches up the two tables by saying that they both have an id column which matches.

Let's see how this works with a simple example:

Create Table1 with an "id" autonumber (I'm using Access of course) and an "a" text column.

Enter the following data:

1 james
2 fred
3 joe

Create Table2 with an "id" autonumber and a "b" text column, and enter:

1 shaw
2 smith
3 deleteme
4 bloggs

Then delete the "deleteme" row (!)

Now when you run the query above you'll get the following returned:

1 james shaw
2 fred smith

The "joe" and "bloggs" rows didn't have any matching entries in the other table, so neither are shown.