Stefanos Kargas Stefanos Kargas - 14 days ago 5
SQL Question

Show results of joined tables even if some values don't match

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...

Answer
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