2. ASPRunnerPro General questions
  3. Lookup across mutliple tables
This topic is locked

Lookup across mutliple tables

 		8/11/2009 8:27:05 AM
ASPRunnerPro General questions
S
 skillsware author

I have a system for clubs to add their events
Three key tables
Members (holds details for each member, NB a member can belong to multiple clubs)

clubs (details of each club)

Members_owners (records which member belongs to which club)

Events (details of each event and which club etc)
To the events table I want to be able to add the member id (as a lookup) so a member can be allocated to that particular event.
The tricky bit I cannot solve is the lookup filter, I know what the sql should be but unsure how to apply to the lookup drop down :
" select members.id, members.member_name from members,Members_owners where Members_owners.mbrid = members.id AND Members_owners.ownereid = " & SESSION("UserID")
This will output all the members to the logined club
C
 		clig 8/15/2009

I have a system for clubs to add their events

Three key tables
Members (holds details for each member, NB a member can belong to multiple clubs)

clubs (details of each club)

Members_owners (records which member belongs to which club)

Events (details of each event and which club etc)
To the events table I want to be able to add the member id (as a lookup) so a member can be allocated to that particular event.
The tricky bit I cannot solve is the lookup filter, I know what the sql should be but unsure how to apply to the lookup drop down :
" select members.id, members.member_name from members,Members_owners where Members_owners.mbrid = members.id AND Members_owners.ownereid = " & SESSION("UserID")
This will output all the members to the logined club


you'd need to do a SQL92 join between the tables - if you have the schema of the tables, their linking column name I can make this a little more SQL like in a response...
S
 		skillsware author 8/15/2009

you'd need to do a SQL92 join between the tables - if you have the schema of the tables, their linking column name I can make this a little more SQL like in a response...


Three key tables
Members (holds details for each member, NB a member can belong to multiple clubs)
ID (int)

Name (varchar)

Address (Varchar)

etc......
clubs (details of each club)

ID (int)

password (varchar)

Name (varchar)

Address (Varchar)

etc......
Members_owners (records which member belongs to which club)

ID (int)

clubid (int) - link to clubs.ID

memberID - link.members.ID
Events (details of each event and which club etc)

ID (int)

Name (varchar)

datestart (date)

dateEnd (date)

clubid (int) - link to clubs.ID

memberID - link.members.ID
From the front end perspective :

  1. Club Logins
  2. Add eventdetails (name, datestart, dateend, (clubid inserted automatically from usersession set at login))
  3. Members Lookup drop down based on members that belong to that club
    Hope this helps
J
 		Jane 8/17/2009

Hi,
you can do the following:

  • create view in the database and join Members and Members_owners tables in this view,
  • use this view as lookup table on the"Edit as" settingsdialog on the Visual Editor tab.