ASP and Access Recordsets

[artsapimp]

I am trying to create an application which queries an access database and displays a list of all people with a specific field empty. The problem I am having is the field is part of a relationship so the records that are empty don't get included in the recordset at all.

The two databases I am opening is [Representative] and [ActionItem]. I would like to display all reps who have not been assigned an ActionItem in a list. When I run the query it runs all of the reps who DO have an ActionItem instead.

How could I possibly resolve this issue other than opening the first database, saving the RepID as a variable, opening the ActionItem database where RepID = varRepID, then re-opening the Representative database again, etc.

This is too much opening of databases and is poor coding. Any help would be greatly appreciated.

Art

[mosquito]

A left join will do the trick.

If you have tables like this
Representative ( repid, name, adress)
ActionItem ( Actid, repid, blabla)

You could retrieve all Representatives with no action items by using this query.

select r.repid, r.name , r.adress 
from Representative r
left join ActionItem a
on r.repid = a.repid
where actid is null

I hope this helps

[artsapimp]

The following is the strSQL I have that kind of works right now. What it's doing is creating all joins correctly, but the ActionItem and Scorecard database is empty until something is written to it. Because of that the recordset always comes up blank. I have commented out the last line and it shows all records.

strSQL = "SELECT Manager.FullRecord, Scorecards.Reviewed, Rep.First_Name, Rep.Last_Name"
strSQL = strSQL & " FROM (Scorecards"
strSQL = strSQL & " LEFT JOIN ActionItems"
strSQL = strSQL & " ON Scorecards.ScorecardID = ActionItems.Scorecard)"
strSQL = strSQL & " LEFT JOIN (Manager"
strSQL = strSQL & " RIGHT JOIN Rep"
strSQL = strSQL & " ON Manager.ManagerID = Rep.Manager)"
strSQL = strSQL & " ON Scorecards.Rep = Rep.RepID"
strSQL = strSQL & " WHERE Reviewed = True"
strSQL = strSQL & " AND Manager.FullRecord = 213"
strSQL = strSQL & " and Reviewed is null"

The database is setup with 4 tables (Manager, Scorecards, ActionItems, Rep).

Thank you for any help.

Art Sapimp

[mosquito]

I would write the query like this:

SELECT m.FullRecord, s.Reviewed, r.First_Name, r.Last_Name
FROM Rep r
  LEFT JOIN Manager m
  ON m.ManagerID = r.manager
  LEFT JOIN (Scorecard s
    LEFT JOIN ActionItems a
    ON s.ScorecardID = a.Scorecard)
  ON s.Rep = r.RepID
WHERE m.FullRecord = 21
  and s.reviewed is null

I removed 'Reviewed = True' because that blocks the 's.reviewed is null'.

[artsapimp]

When I removed that line it still came up with no people listed. I ended up playing with your code and still could not get it to work. Eventually I opened Access and created the query I wanted, went to SQL mode and copied and pasted it. I don't know why it worked like it did but I would love to know. If someone could explain this a little better for me I would really appreciate it. Thanks.

Here's the code that worked

strSQL = "SELECT Manager.FullRecord, Scorecards.Reviewed, Rep.First_Name, Rep.Last_Name"
strSQL = strSQL & " FROM Manager"
strSQL = strSQL & " 	RIGHT JOIN (Scorecards"
strSQL = strSQL & " 		RIGHT JOIN Rep"
strSQL = strSQL & " 		ON Scorecards.Rep = Rep.RepID)"
strSQL = strSQL & " 	ON Manager.ManagerID = Rep.Manager"
strSQL = strSQL & " WHERE Scorecards.Reviewed Is Null AND Manager.FullRecord = 213"

Art

[mosquito]

The Left and right join are both of the "outer join" type. I'll explain it with an example.
Table A(id, name) ==> id is primary key
Table B(id, Aid, score) ==> id is primary key

The tables have the following values:

Table A     Table B
-------     -------
1, John     1, 2, 100
2, Pete     2, 2, 75
3, Eddy     3, 3, 55
            4, 2, 50

When we do a left join from table A to table B with the query

select q.name, b.score from A left join B on A.id = B.Aid

we will have the following result

name, score
-----------
John, NULL
Pete, 100
Pete, 75
Eddy, 55
Pete, 50

as you can see all records from table A are taken, and the matching records from table B. If we want the records from table A that don't have a matching record in table B, we use the query

select q.name, b.score from A left join B on A.id = B.Aid where B.id is null

The B.score is null filters out all records that have a value for score. Please note that you can only use this method when you are sure that there is always a value in the id column (thats why I filtered on the id column, and not on the score column. id is primary key, and therefore can not be NULL)

Another way to find the records in table A that don't exist in table B is with a subquery.

select A.name 
from A
where id not in (select Aid from B)

This method also works but is a LOT slower then the outer join.

The difference between a LEFT JOIN and a RIGHT JOIN is simply that with a left join you take all records from table A and the matches in table B, and with a right join you take all records from table B, and the matches from table A

I hope this helps.

[artsapimp]

I have read many tutorials, books, source code, etc. and have never understood the difference between joins. I use RIGHT JOINS on a daily basis and I guess it's been luck but it always works like I want it to.

Thank you very much for your help.

Art

[artsapimp]

The easiest way to explain the project goal I am working toward is to describe a bulletin board.

Tables in the database

PHP Code:

Category:
-CategoryID (autonumber)
-Category (text)

Topic:
-TopicID (autonumber)
-Topic (text)
-Category (number) 


Example information for each table

PHP Code:

Category:
1, Drinks
2, Snacks
3, Meals

Topics:
1, Coke, 1
2, Pepsi, 1
3, Orange Juice, 1
4, Crackers, 2
5, Peanuts, 2
6, Hamburger, 3
7, Steak, 3 


SQL statement

PHP Code:

SELECT Category.CategoryID, Category.Category, Topic.TopicID, Topic.Topic
FROM Topic RIGHT JOIN Category ON Category.CategoryID = Topic.Category 


How would I loop through the recordset showing the data like a messageboard would?

Drinks
     Coke
     Pepsi
     Orange Juice
Snacks
     Crackers
     Peanuts
Meals
     Hamburger
     Steak

Every time I try I get either a BOF or EOF error or it just shows the first category.

Thanks for any help.

[mosquito]

this code simply loops through the recordset and writes the result. If you want to make titles and stuff, you can join everything together (heavy on db server) and simply loop through the single recordset or retrieve a titles recordset, and then retrieve topics per title (heavier on the webserver).

the big recordset would be something like
title message
-----------------
test mess1
test mess2
test2 mess1
test2 mess2
test2 mess3

so you print the title, and then the message, and as long as the title remains the same, only print the messages.


[CODE]
<html>
<body>
<% test %>
</body>
</html>

<%

Sub test()
Dim conn
Dim rs
Dim cm

Set conn = CreateObject("adodb.connection")
Set rs = CreateObject("adodb.recordset")
Set cm = CreateObject("adodb.command")

conn.ConnectionString = "your connectionstring"
conn.Open

If conn.State = adStateOpen Then
Set cm.ActiveConnection = conn
cm.CommandTimeout = 10
cm.CommandText = "select * from message"
Set rs = cm.Execute

If rs.State = adStateOpen Then
Do While Not rs.EOF
response.write "blablabla" & rs.Fields("message").Value
rs.MoveNext
Loop
rs.Close
End If

End If

Set rs = Nothing
Set cm = Nothing
conn.Close
Set conn = Nothing
End Sub
%>
[CODE]

[artsapimp]

That is pretty much what I've done now. It's running a few loops and comparing each one to each other. I'm not good at explaining it, but it's working.

Thanks again everyone.