|CoverYourASP --> Snippets|
|"Snippets" are mini-articles - they allow me to very quickly add answers to your frequently asked questions, and add brief explanations for topics that you've searched for without success. So, everytime you send an email or perform a search, chances are a new snippet will be the result!|
17 Sep: Q&A;: How to retrieve related data from two or more tablesAbdel:
"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"
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:
Create Table2 with an "id" autonumber and a "b" text column, and enter:
Then delete the "deleteme" row (!)
Now when you run the query above you'll get the following returned:
1 james shaw
The "joe" and "bloggs" rows didn't have any matching entries in the other table, so neither are shown.