1

Access Database

table contacts
--------------
id
surname
name


table relations
---------------
contact_id
relation_id

Both contact_id and relation_id are foreign keys referenced to table contacts' id

I want to execute a query to get both the contact's surname/name and the relation's surname/name if a relation for the current contact exist. If it doesn't exist I want to get the contact's surname/name and blank values for the relation's fields.

All this in one query

EDIT:

I used left join. I am running the query using VB.NET:

Dim myOleDbDataReader As OleDbDataReader = _
    New OleDbCommand( _
    "SELECT c.id           AS contact_id " & _
    "     , c.surname      AS contact_surname " & _
    "     , c.name         AS contact_name " & _
    "     , c2.id          AS related_id " & _
    "     , c2.surname     AS related_surname " & _
    "     , c2.name        AS related_name " & _
    "FROM ((contacts c " & _
    "LEFT JOIN relations r " & _
    "ON c.id = r.contact_id) " & _
    "INNER JOIN contacts c2 " & _
    "ON c2.id = r.relation_id)" _
    , connection).ExecuteReader()

I get OleDbException: Join expression not supported.

They say in another post that: "Access won't let you use conventional joins in the where clause when you use LEFT/RIGHT/INNER JOINS in the FROM clause. It is probably intentional to get you to buy more expensive software." - ( Is the join expression not supported by MS Access? )

It is not exactly that. From some examples I tried I came to the conclusion that:

Access won't let you use outer joins (LEFT/RIGHT) together with one or more INNER JOINS. What in John Carmack's name can I do? I would like to avoid seperate select queries. Please help...

Community
  • 1
  • 1
Stefanos Kargas
  • 10,547
  • 22
  • 76
  • 101

1 Answers1

3
SELECT c.id       AS contact_id
     , c.surname  AS contact_surname
     , c.name     AS contact_name
     , c2.id      AS related_id
     , c2.surname AS related_surname
     , c2.name    AS related_name
FROM contacts c
  LEFT JOIN relations r
    ON c.id = r.contact_id
  JOIN contacts c2
    ON r.relation_id = c2.id

The above does NOT work in MS-Access.

This is slightly different (two left joins) but it works:

SELECT c.id       AS contact_id
     , c.surname  AS contact_surname
     , c.name     AS contact_name
     , c2.id      AS related_id
     , c2.surname AS related_surname
     , c2.name    AS related_name
FROM contacts c
  LEFT JOIN
    ( relations r
      LEFT  JOIN contacts AS c2
        ON r.relation_id = c2.id
    )  
    ON c.id = r.contact_id

Despite the second LEFT JOIN, it will give same result set, since the second LEFT JOIN involves a Foreign Key relationship (in the direction from many -> one).

To have a LEFT JOIN with an INNER JOIN you could use:

SELECT c.id       AS contact_id
     , c.surname  AS contact_surname
     , c.name     AS contact_name
     , g.id       AS related_id
     , g.surname  AS related_surname
     , g.name     AS related_name
FROM contacts c
  LEFT JOIN
    ( SELECT r.contact_id
           , c2.id      
           , c2.surname
           , c2.name 
      FROM relations r
        INNER JOIN contacts AS c2
          ON r.relation_id = c2.id
    )  AS g
    ON c.id = g.contact_id
ypercubeᵀᴹ
  • 113,259
  • 19
  • 174
  • 235