Shows rows that are not part of a relation

[ralphtrent]

Hello
I am building a Message Board. I have a screen that i need to show the topics, total message for that topic, and the last update to that topic. I have two tables, The Topics Table, and the Messages Table. Not all topics have message yet so when i join the two tables I will not get the data I need. What I have done though is create a relation on the two columns. I show what i can but then I do a trick to show the rest of the data, (meaning where the data is not joined). I want to know if there is a real way of doing this or is my trick good enough. Here is my relationship code:

'go through all the rows in my dtActiveTopics Table
For each r in objDataSet.tables("dtActiveTopics").rows
Dim childr() as DataRow
childr = r.getChildRows("Relation")
strResultsHolder += "<tr Class='"& GetClass &"'><td /><td><a href='..\list\ListBoardsDetails.aspx?MSG_CAT_ID=" & r("MSG_CAT_ID") & "'>" &r("MSG_CAT_DESC") & "</a></td>"

dim foundRow as boolean
Dim theChildRow as Datarow

'declare a variable that tells me if there is a realtionship
foundRow = false

'go through the relation and print out the data
for each theChildRow in Childr
foundRow = true
strResultsHolder += "<td>" & theChildRow("Total") & "</td>"
strResultsHolder += "<td>" & theChildRow("LastMSG") & "</td>"
next

'if the value from the dtActiveTopics table does not have a matching
'value in my other table then just print out some stock info
if foundRow = False then strResultsHolder += "<td>0</td><td>-</td>"
strResultsHolder += "</tr>"
next

Let me know if my explinations do not make sense. In the end I get what I am looking for, but is there a vb way of doing this.

Here is an example of the results

Topic Message Count Last Update
topic1 1 11.29.2004 'Relation found
topic2 0 - 'No Relation Found
topic3 2 11.24.2004 'Relation Found
topci4 0 - 'No Relation Found

-Thanks

 

[jen89]

What type of database are you using? You need something called an outer join. If you are using Oracle, you can do something like this:
where messages.seqnum = topics.messageseqnum (+)

this brings back everything on the side of the relationship with the +, and only the joined stuff from the other side