BradleyS BradleyS - 10 months ago 73
SQL Question

SQL Join fields from 3 tables

I have 3 tables of data that I need to join to make one table and show the following fields:
u.master_id, p.surname, p.forename1, c.number, u.status

Here are the table details:

Table 1 (person p)
Field name = entity_id (number)
Field name = surname(text)
Field name = forename1(text)
Field name = auditflag

Table 2 (users u)
Field name = master_id (number)
Field name = email (number)
Field name = status (text)
Field name = auditflag

Table 3 (contact c)
Field name = master_id (number)
Field name = entity_id (number)
Field name = number (text = holds the actual email address)
Field name = auditflag

The master_id field from table 2 and 3 links to the person entity_id ( master_id=p.entity_id )

The email field from table 2 links to the entity_id in table 3 ( ) to get the number

The auditflag is used to mark each as an active record i.e. ( p.auditflag=1 AND u.auditflag=1 AND c.auditflag=1 )

I'm using Excel 2010 VBA - joining to a PostGreSQL database to get the data.
Below is the code I am using to join the 2 tables which works fine, but my problem is that "email" only shows a number because the actual address is in a 3rd table (Contact) under the field called "number".

I don't know how to include the 3rd table to show the field c.number in my query.

Const sqlconnection = "Provider=oledb;"

Dim conn As New Connection
conn.ConnectionString = sqlconnection

Dim rs As Recordset

Dim prs As String
prs = "SELECT u.master_id, p.surname, p.forename1,, u.status " _
& "FROM users u INNER JOIN person p ON u.master_id=p.entity_id " _
& "WHERE u.auditflag=1 AND p.auditflag=1 " _
& "ORDER BY u.master_id "

Set rs = conn.Execute(prs)
With ActiveSheet.QueryTables.Add(Connection:=rs, Destination:=Range("A1"))
End With

cco cco
Answer Source

You have all the pieces; all you need to do is add the third table to the query and select the columns you want.

Dim prs As String
    prs = "SELECT u.master_id, p.surname, p.forename1, c.number as email, u.status " _
        & "FROM users u INNER JOIN person p ON u.master_id=p.entity_id " _
        & "    INNER JOIN contact c ON c.master_id=p.entity_id and " _
        & "WHERE u.auditflag=1 AND p.auditflag=1 AND c.auditflag=1 " _
        & "ORDER BY u.master_id "

As written, this query will only get users that have active (auditflag=1) person rows and active contact rows. Changing to left joins and moving the auditflag checks to the join condition would allow returning users without active rows in all three tables.

As a side note, I find the column names in your tables are actively working against easily understanding what they mean; if you can, I'd suggest re-thinking the names you're using.