Stuck on approach to associate data from related tables

[awasson]

Today is one of those days... Earlier I found that I was playing tricks on myself by burying calls to functions in various parts of an app while I was testing and then forgot to pull them out as I moved on to other parts. Obviously, I can't be trusted today

Anyway, I am working with a membership system I developed several years ago that spans across a dozen or so tables to handle member profiles, education, employment and services. Today, I would like to query 3 tables on database to create a "services offered" list of checkboxes and pre-check them for the member in view. The tables are: Members, Categories, CategoriesLinkTable.

Members {
ID int;
firstname varchar();
lastname varchar();
etc...
}

Categories {
ID int;
CatTitle text;
}

CategoriesLinkTable {
ID int;
individualID int;
CatID int;
}

I can grab all the services offered from the Categories table with a simple query as follow:

 SELECT ID, CatTitle FROM Categories ORDER BY ID ASC;

I can grab all of the services that a particular member provides by making a more complex joined query as follows (MS Access with a VBScript variable memberid):

"SELECT Categories.ID AS Categories_ID, Categories.CatTitle, CategoriesLinkTable.ID AS CategoriesLinkTable_ID, CategoriesLinkTable.IndividualID, Members.ID AS Member_ID FROM Members INNER JOIN (Categories INNER JOIN CategoriesLinkTable ON Categories.ID = CategoriesLinkTable.CatID) ON Members.ID = CategoriesLinkTable.IndividualID WHERE CategoriesLinkTable.IndividualID =" & memberid & " ORDER BY Categories_ID ASC;"

I'm trying to figure out if I can grab the data so that I can loop through it to produce a list of checkboxes and pre-check the ones that the member in question offers. It occurs to me that I might have to run both queries and then amalgamate the information during the looping process.

Any thoughts?

Thanks a lot!!

[Dan Grossman]

When you want all the rows from one table (Categories) even if there is no match in another table (CategoriesLinkTable), use an OUTER join instead of an INNER join.

SELECT
  Categories.ID AS Categories_ID, 
  Categories.CatTitle, 
  CategoriesLinkTable.ID AS CategoriesLinkTable_ID, 
  CategoriesLinkTable.IndividualID, 
  Members.ID AS Member_ID 
FROM
  Categories
LEFT OUTER JOIN
  CategoriesLinkTable
ON 
  Categories.ID = CategoriesLinkTable.CatID
LEFT OUTER JOIN
  Members
ON
  Members.ID = CategoriesLinkTable.IndividualID AND Members.ID = ?

The Members.ID column will be NULL if that member does not have a row for that category, which is an unchecked checkbox for your app.

[awasson]

Thanks Dan...

I see exactly what you're saying there and have to admit, that solution had totally escaped me. I'm getting errors trying to run the query at the moment but I'll do some reading up on Outer Joins to see where I'm going wrong.

Thanks again!