Getting records from 2 tables

[lavaghman]

Here's a statement I wrote, which selects records from a table called WHYTES in a database called WHYTES.MDB. I want a statement which selects records from two tables, called whytes and whytes1. Can anyone help me out? Thanks in advance.

SELECT *
FROM whytes
WHERE ARTIST LIKE '%varARTIST%' AND TITLE LIKE '%varTITLE%'
ORDER BY ARTIST ASC

 

[bperry]

What you will be doing is Joining the two tables, and you will need to indicate just how they should be joined. Usually the join is made on some common column between the two tables (i.e. like EmplId, for example)

Does Access support the ANSI92 syntax? If so, then you will do something like this:

SELECT *
FROM whytes
INNER JOIN whytes1
ON whytes.columnname = whytes1.columnname
WHERE ARTIST LIKE '%varARTIST%' AND TITLE LIKE '%varTITLE%'
ORDER BY ARTIST ASC

Also, the *type* of join is determined by the results you want. an INNER JOIN, as shown above, will only return rows that exist in *both* tables. If you wanted instead, say, all rows from whytes regardless of whether there were any matching rows in whytes1, then you would replace INNER JOIN with LEFT OUTER JOIN (or just LEFT JOIN for short).

hope this helps,
brian perry

 

[lavaghman]

Thanks Brian,
just what I was after!