CODEWITHSUNDEEP
sqlms-access
dan
dan

Reputation: 3519

Select from 2 tables with a left join

Here is probably an easy one if your used to SQL, which I'm not.

I have this query:

SELECT * 
FROM myTable 
LEFT JOIN anotherTable 
    ON myTable.myField=anotherTable.anotherField

Actually, this query returns everything from myTable which meets the join condition (as far as my comprehension goes). This query works great, but I would like to select another field from another table. What I would like to do is something like this:

SELECT myTable.*, myTable2.aSpecificField 
FROM myTable, myTable2 
LEFT JOIN anotherTable 
    ON myTable.myField=anotherTable.anotherField 
WHERE myTable.id = myTable2.id

However, this doesn't work, I get an error message about the LEFT JOIN. I would like the aSpecificField from myTable2 to be added to the rows where the ID of both tables match.

Can you help me building this query correctly? Thank you.

Upvotes: 0

Views: 175

Answers (3)

Fionnuala
Fionnuala

Reputation: 91316

The Microsoft Access query design window lets you use wizards or drag-and-drop to create queries. You can then tidy up the SQL in design view. Add your tables to the design grid, drag the joining fields from one table to the next, and then select the fields you want.

When you wish to advance your knowledge of Access SQL, you can read ...

These are for Jet (<2007), but ACE (>=2007) is not that different as regards SQL

Fundamental Microsoft Jet SQL for Access 2000
Intermediate Microsoft Jet SQL for Access 2000
Advanced Microsoft Jet SQL for Access 2000 Jet Database Engine

Background on design

Fundamentals of Relational Database Design, Paul Litwin, 2003

An often recommended MS Access book

Access cookbook

Upvotes: 1

flurdy
flurdy

Reputation: 3972

So you want to return the rows where the ids match for both tables? And not where they dont match? So not a left or right join but a plain join?

Select tab1.*,tab2.* from mytable tab1 
  INNER JOIN mytable2 tab2 
    ON tab1.id = tab2.otherid

Or if you have 3 tables to join?

Select tab1.*,tab2.*,tab3.* from mytable tab1 
  INNER JOIN mytable2 tab2 
    ON tab1.id = tab2.otherid 
  INNER JOIN mytable3 tab3 
    ON tab1.id = tab3.anotherid

Replace tabX.* with specific fields if needed?

Upvotes: 0

JonH
JonH

Reputation: 33143

List out your fields rather then using *. Like so:

SELECT m.Field1, m.Field2, m2.Field1 FROM
MyTable m LEFT JOIN MyTable2 m2 ON m2.ID=m1.ID

Per your comment what relation does table3 have with another table? Just include it based on that relationship

SELECT m.Field1, m.Field2, m2.Field1, m3.Field1 FROM
MyTable m LEFT JOIN MyTable2 m2 ON m2.ID=m1.ID LEFT JOIN MyTable3 m3 ON m3.ID=m2.ID

Upvotes: 0

Related Questions