SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Wizard bronze trophy
    Join Date
    Oct 2001
    Location
    Vancouver BC Canada
    Posts
    2,032
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Stuck on approach to associate data from related tables

    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:
    Code:
     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):
    Code:
    "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!!
    Andrew Wasson | www.lunadesign.org
    Principal / Internet Development

  2. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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.

    Code:
    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.
    Last edited by Dan Grossman; Nov 13, 2009 at 20:21.

  3. #3
    SitePoint Wizard bronze trophy
    Join Date
    Oct 2001
    Location
    Vancouver BC Canada
    Posts
    2,032
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)
    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!
    Andrew Wasson | www.lunadesign.org
    Principal / Internet Development


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •