CoverYourASP --> Snippets

Free membership

Join in the fun! Sign in
Member Services

Site navigation
Download the entire site!
Search my articles
Free Magazines
Browse the directory

Send me feedback
Buy my boxer shorts

Recommend this page
Printer-friendly page

Resources I recommend
Link to my site
Advertising slashed!
About your privacy
Legal stuff
Site statistics
26 active users
2146 visitors today
1906 pages today
(only part of today)
Tools I use

Copyright © 1999-2016 James Shaw.
All rights reserved.

RSS submissions

Now open source with SourceForge!

"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 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"


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

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

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.

Featured sponsor
My favorite resources

I share my content

Supporting ASPRSS

Do you need a quick and easy way to link to my articles? All the information you need is published with ASPRSS...

CoverYourASP Mugs, T-shirts, caps - even Boxer shorts...
I don't make a penny from these, but they're a lot of fun! Don't you need a new mouse mat?