Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2004
    Posts
    2

    Exclamation Unanswered: Multiple Table Queries

    I've been doing a significant amount of development with ASP and Dreamweaver, but I've run into one problem I just can't seem to solve.

    I have a simple set of master > Detail page sets, where a URL passes a variable from a selection page to a details set. Not too horribly tough, right? Well, I want the details page to show some more information about this table and its relationships to others. (see attached file for visual)

    I have a table named memebers with these fields:
    ===============================
    memberID (key)
    Name
    Email
    (...and several other fields not really relevant to post)

    Another table, named groups with these fields
    ===============================
    groupID (Key)
    groupName
    groupDetails

    And a third table MembersInGroups
    containing each relationship of a group to members
    ===============================
    ID (key)
    GroupName (foreign Key to groupID
    MemberName (foreign Key to memberID

    ==============================================
    SO, here's what I have to do:
    ==============================================

    I need to show what members are in a group in a group detail page, and I also have to show what groups a member is in. How would I do that using the unique variable passed from the master to the detail page for groups and members?


    And if you're REALLY good......

    ...How would I construct a page to delete these relationships?

    - VWJETTAV6
    Attached Thumbnails Attached Thumbnails table.jpeg  

  2. #2
    Join Date
    Sep 2003
    Posts
    39
    You should start off by not using the membername in the membersInGroups but the memberId (or at least add the memberId to the membersInGroups table). Same for the groupName.

    Next:

    Code:
    select
      name
    from
      members
        inner join
      membersInGroups
        on membersInGroups.groupId = <group id>
    and for each member:

    Code:
    select
      groupname
    from
      membersInGroups
        inner join
      groups
        on membersInGroups.groupId = groups.groupId
    where
      membersInGroups.memberId = <member id>
    I didn't understand your question about deleting relationships. I'm sure you're able to write a simple delete statement, right?

  3. #3
    Join Date
    Jan 2004
    Posts
    2

    Information based on URL variable passes

    << Please see image attached in previous postings >>

    I now have relationships defined between my tables.

    I have a link to a paged named groups.asp. This page links to a detail paged named groups detail.asp.

    I need to know how to show what members are in a group based on the URL variable passed from the groups page to detail. I'm using the primary key named groupID in table groups to do this.

    Next, I have a page named
    membership, and a page named membership_)detail. Just as in groups, I'm passing a variable from one page to another, in this case, I'm using the

    memberID.

    So what would the SQL syntax look like to display this information after creating the joins using the foreign key relationships identified in MembersInGroups?

  4. #4
    Join Date
    Dec 2003
    Posts
    454
    In your database design, it is better to use MemberID, GroupID in the table MembersInGroups instead of MemberName, GroupName.

    If you pass MemberID to your first query, the SQL statement is like this:

    SELECT *
    FROM Members INNER JOIN MembersInGroups
    ON Members.MemberID = MembersInGroups.MemberID
    WHERE MembersInGroups.GroupID = @groupID

    If you pass GroupID to your second query, the SQL statement is like this:

    SELECT * FROM Groups INNER JOIN MembersInGroups
    ON Groups.GroupID = MembersInGroups.GroupID
    WHERE MembersInGroups.MemberID = @memberID
    Last edited by gyuan; 01-19-04 at 16:21.

Posting Permissions

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