SQL join query

[Laurie Comerford]

Hi,

I'm not sure that this is the optimum NG, but I am using ADO with VB6 on
an MS Access 2007 database and having troubles defining the query string.

The database contains 7 tables

The main table contains several fields of random data and has links to
the ID field in each of 6 other tables. Each of the other tables has an
ID field and a data field.

I need to create a query which can get the data from each of the other 6
tables.

Using "Field" as a generic name for any field in a table, and
"Condition" to match any expression, I have three working queries:

1 Simple query from main table
SELECT * FROM Maintable WHERE Condition ORDER BY Field

2 Simple left join query on the main table and one other table
SELECT MT.Field, A.Field
FROM Maintable AS MT LEFT JOIN TableA AS A ON MT,ID_A = A.ID
WHERE Condition ORDER BY Field

and

3 Left join query getting data from the main table and two other tables
SELECT MT.Field, A.Field, B.Field
FROM (Maintable AS MT LEFT JOIN TableA AS A ON MT,ID_A = A.ID )
LEFT JOIN TableB AS B ON MT.ID_B = B.ID
WHERE Condition ORDER BY Field

I found this query after much experimenting with bracket placement and
information on the web, but don't understand the implications of the
bracket placement. I haven't been able to find what I need to extend to
get data from more than two additional tables.

What I need is the syntax to extend these queries so I can (ultimately)
create a full extract of all data into a flat table like:

SELECT MT.Field, A.Field, B.Field, C.Field, D,Field, E.Field, F.Field
FROM (Maintable AS MT LEFT JOIN TableA AS A ON ON MT,ID_A = A.ID )
LEFT JOIN TableB AS B ON MT.ID_B = B.ID
LEFT JOIN TableC AS C ON MT.ID_C = C.ID
LEFT JOIN TableD AS D ON MT.ID_D = D.ID
LEFT JOIN TableE AS E ON MT.ID_E = E.ID
LEFT JOIN TableF AS F ON MT.ID_F = F.ID
WHERE Condition ORDER BY MT.Field

Using the third query as an example I have been moving brackets around
and trying other combinations, but it all keeps telling me there is an
error without giving any guidance as to where the error is.


Regards


Laurie Comerford

 

[Doug Robbins - Word MVP on news.microsoft.com]

Use the Access query developer to set up the query that you want and then
switch to the SQL view if you want to get hold of the SQL for the query.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com