SQL statement from two tables

Question

Hello,

I would like to know if it is possible, to select certain columns from one table, and another column from a second table, which would relate to a non imported column in the first table. I have to obtain this data from access, and do not know if this is possible with Access, or SQL in general.

Answer 1

What you are looking for are JOINs:

http://en.wikipedia.org/wiki/Join_(SQL)

You need primary keys for the referenced data sets and foreign keys in the first table.

Answer 2

Assuming the following table structure:

CREATE TABLE tbl_1 (
    pk_1 int,
    field_1 varchar(25),
    field_2 varchar(25)
);

CREATE TABLE tbl_2 (
    pk_2 int,
    fk_1 int,
    field_3 varchar(25),
    field_4 varchar(25)
);

You could use the following:

SELECT t1.field_1, t2.field_3
FROM tbl_1 t1
INNER JOIN tbl_2 t2 ON t1.pk_1 = t2.fk_1
WHERE t2.field_3 = "Some String"

In regard to Bill's post, there are two ways to create JOIN's within SQL queries:

• Implicit - The join is created using the WHERE clause of the query with multiple tables being specified in the FROM clause

• Explicit - The join is created using the appropriate type of JOIN clause (INNER, LEFT, RIGHT, FULL)

It is always recommended that you use the explicit JOIN syntax as implicit joins can present problems once the query becomes more complex.

For example, if you later add an explicit join to a query that already uses an implicit join with multiple tables referenced in the FROM clause, the first table referenced in the FROM clause will not be visible to the explicitly joined table.

Answer 3

I'm not 100% sure I understand your question.

Is the following true:

Your first table is imported from somewhere else. You are only importing some columns. You want to build a query which references a column which you haven't imported.

If this is true, it's just not possible. As far as the Access query engine in concerned the non-imported columns don't exist.

Why not just import them as well?

Answer 4

But primary keys make the query more efficient