Retrieving recordset results from two tables.

Getting Started
1

I have a database which I used to pull information from one table (addressbook). See the code below.

sql = "SELECT NRef, id, company, contactname FROM addressbook"

But now I’ve added a second table (supid) and I want the second table to store the id information, while the original table (addressbook) has the company and contact name information. How do I modify this line of code to retrieve the recordset results from two tables?

2

with SQLite?

3

Yes, sorry I just realized (and tried to edit my first post) but the sql is a property, type String.

4

@Kayla Gordon you’ll want to use a SQL Join to connect the two tables in a single recordset.

For example

sql = "SELECT addressbook.NRef, addressbook.id, addressbook.company, addressbook.contactname, supid.name FROM addressbook INNER JOIN supid ON supid.id = addressbook.id"

you’ll obviously have to use actual column names from supid though.

You can also use aliases for the table names.

sql = "SELECT a.NRef, a.id, a.company, a.contactname, s.name FROM addressbook AS a INNER JOIN supid AS s ON s.id = a.id"

to reduce the amount of typing & simplify the reading of the statement.

HTH
Wayne

5

Wayne - would you be able to explain the end part - in complete layman’s terms :slight_smile:

INNER JOIN clients ON clients.id = addressbook.id"

I understood the original post to be that the id column has moved from the table called addressbook, to the table called supid.
Therefore I am wondering what supid.id=addressbook.id refers to?

Thanks.

6

Richard there will need to be a link between the two tables in order to get a match. Without knowing what the second table looks like I created an example. Obviously it will need to be adjusted to suit the data.

7

Wow - I have looked at 3 different sites on the Join clause, and I still cannot understand it :frowning:

If I had 2 tables (TableA, and TableB) - how would I combine:

sql = "SELECT FirstName, LastName FROM TableA"

with:

sql = "SELECT Company FROM TableB"

If I can see the answer, I can then TRY and work out what it is doing :frowning:

Thanks.

8

Richard,
When using a relational database with a one-to-many relationship there will be a foreign key in the many table that refers to the one tables primary key. This is used to Join the two tables in queries. So TableB would have to include the primary key of TableA for the Join to work.
Cheers
Wayne

9

[quote=196337:@Richard Summers]Wayne - would you be able to explain the end part - in complete layman’s terms :slight_smile:

INNER JOIN clients ON clients.id = addressbook.id"

I understood the original post to be that the id column has moved from the table called addressbook, to the table called supid.
Therefore I am wondering what supid.id=addressbook.id refers to?

Thanks.[/quote]
An INNER JOIN is very much like a WHERE clause, so

select table1.name, table2.salary from table1 inner join table2 on (table1.id = table2.id)

could also be written as

select table1.name, table2.salary from table1, table2 WHERE table1.id = table2.id

Simon.