CODEWITHSUNDEEP
sqlms-access
U.Savas
U.Savas

Reputation: 139

Joining two tables listing all the results from the first table

I have two tables related to each other. The first table has one or more number of the same attribute on the latter table. (there is a one-to-many relationship) thus the rows on the latter table includes basically the following columns: (PK, FK, attribute). Some rows of the first table does not have an attribute and are not recorded in the latter table.

I would like to list all the rows in the first table with an extra column for the attribute from the latter table. when I use inner join or where clause I cannot list the results that have no attribute. However what I want to do is to list all the rows from the first table, and if there is no attribute for it I want to make the extra column null, and if it has more than one attribute I want to concatenate them in that single column.

I am aware that I am not sharing any code here and I am asking the question in an abstract way because first I need to know which tool to use and in what way. I tried joins and sub-queries with select statement as well as methods such as ISNULL but I was not sure what I was doing and it did not work for me.

PS: I am new to the site as you can observe. Thus if you think this is not a proper way of asking questions and if you suggest removing this question off the forum I will no question asked.

Upvotes: 0

Views: 92

Answers (1)

Skippy
Skippy

Reputation: 1590

SELECT TableA.*, TableB.Attribute
FROM TableA LEFT JOIN TableB ON TableA.PK = TableB.FK

Edit: I just read the original question more fully and realised that this is not quite what you're after. This will give you the NULL value you wanted if there is no related data in TableB, however you will need to write a function if you want multiple related values in TableB concatenated into the one row for a given TableA record. Create a new module and in here add the following function. I am assuming here that your PK and FK columns are Long Integer and the Attribute values are String. I am also assuming that you're working in MS-Access.

Public Function ListAttributes(lngPK As Long) As String

    Dim rsAttributes As DAO.Recordset
    Dim strResult as String

    Set rsAttributes = CurrentDB.OpenRecordset("SELECT Attribute FROM TableB WHERE FK = " & lngPK)
    Do While Not rsAttributes.EOF
        strResult = strResult & ", " & rsAttributes!Attribute
        rsAttributes.MoveNext
    Loop
    If strResult <> "" Then
        strResult = Mid(strResult, 3)
    End If
    ListAttributes = strResult
    Set rsAttributes = Nothing

End Function

Your query then becomes

SELECT *, ListAttributes(PK) FROM TableA

Note that you will now get an empty string instead of NULL for rows in TableA that have no corresponding rows in TableB, but you can always use an IIf function call to fix this if necessary.

Upvotes: 1

Related Questions