| Author |
 Topic  |
|
|
Davio
Development Team Member
Jamaica
12217 Posts |
 Posted - 03 June 2005 : 17:29:11
|
Hey guys.
I'm trying to select a set of records that matches the TOPIC_ID. These records also contain the MEMBER_ID. I also need the members name from the MEMBERS table. So doing a select query that matches the MEMBER_ID from both tables works. The query looks like this:
strSql = "SELECT S.E_ID, S.E_MEMBER_ID, S.E_ARRIVAL, S.E_COMMENTS, S.E_HOWMANY, S.E_NICKNAME, M.M_NAME "
strSql = strSql & "FROM " & strTablePrefix & "SIGNUP AS S, " & strMemberTablePrefix & "MEMBERS AS M "
strSql = strSql & "WHERE S.E_TOPIC_ID = " & intTopicID & " "
strSql = strSql & "AND S.E_MEMBER_ID = M.MEMBER_ID "
That's easy enough. But the problem arises when the members don't have a MEMBER_ID in the SIGNUP table. These are what you call guest users.
How can I include these guest users that don't have a corresponding MEMBER_ID in the MEMBERS table? Because the query only gets the records whose MEMBER_ID matches the MEMBER_ID in the MEMBERS table.
The script is part of the Calendar Events mod where users can signup to an event. The admin wants to add users to the list and these users are not registered on the forum. So you have both members and non-members on the signup list. While getting the member name for the members and storing the names of the non-members in the NICKNAME field.
If I can't get a query to do this, I might store the member name instead of the member_id in the table. |
Support Snitz Forums
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 03 June 2005 : 18:54:23
|
strSql = "SELECT S.E_ID, S.E_MEMBER_ID, S.E_ARRIVAL, S.E_COMMENTS, S.E_HOWMANY, S.E_NICKNAME, M.M_NAME "
strSql = strSql & "FROM " & strTablePrefix & "SIGNUP AS S, " & strMemberTablePrefix & "MEMBERS AS M "
strSql = strSql & "WHERE S.E_TOPIC_ID = " & intTopicID & " "
strSql = strSql & "AND (S.E_MEMBER_ID = M.MEMBER_ID OR S.E_MEMBER_ID Is Null"
|
Snitz 3.4 Readme | Like the support? Support Snitz too |
 |
|
|
Davio
Development Team Member
Jamaica
12217 Posts |
Posted - 03 June 2005 : 19:27:59
|
Thanks Rui. The guest member id is set to 0. So I used S.E_MEMBER_ID = 0 instead of Is Null.
But that didn't work as I expected. I oly have 1 guest user in the signup table and it shows over 100 times in the result query.
Here is the query output:
SELECT S.E_ID, S.E_MEMBER_ID, S.E_ARRIVAL, S.E_COMMENTS, S.E_HOWMANY, S.E_NICKNAME, M.M_NAME FROM FORUM_SIGNUP AS S, FORUM_MEMBERS AS M WHERE S.E_TOPIC_ID = 1209 AND (S.E_MEMBER_ID = M.MEMBER_ID OR S.E_MEMBER_ID = 0)
Here is some info on the database.
The records I want to get from the table is highlighted:
Some of the table properties:
This is what the result looks like when I run it in access:
 |
Support Snitz Forums
|
Edited by - Davio on 03 June 2005 19:30:03 |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 03 June 2005 : 19:35:37
|
strSql = "SELECT S.E_ID, S.E_MEMBER_ID, S.E_ARRIVAL, S.E_COMMENTS, S.E_HOWMANY, S.E_NICKNAME, M.M_NAME "
strSql = strSql & "FROM " & strTablePrefix & "SIGNUP AS S LEFT JOIN " & strMemberTablePrefix & "MEMBERS AS M "
strSql = strSql & "ON S.E_MEMBER_ID = M.MEMBER_ID "
strSql = strSql & "WHERE S.E_TOPIC_ID = " & intTopicID
|
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
Davio
Development Team Member
Jamaica
12217 Posts |
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
Davio
Development Team Member
Jamaica
12217 Posts |
Posted - 27 June 2005 : 19:11:16
|
Ok, I got another sql query that have spent hours on, but still not getting it.
A user wants a feature that allows you to send an email reminder, to members of a usergroup, that are not signed up for an event. So you have an event called "Snitz Concert: staring Huwr and Richard". You have a signup list where members can signup for the event to shoe that they are coming.
Now the admin wants to send an email reminder to members who HAVEN'T signed up for the event. He also wants to send this email to a select usergroup (part of the usergroup mod).
The persons signed up for the event are in a table called FORUM_SIGNUP.
Table holding the members that belong to each usergroup is called FORUM_USERGROUP_MEMBERS.
I need the username, email, etc. info from the FORUM_MEMBERS table.
Scenario:
Admin write the email in a text box. Selects the usergroup he wants to send an email reminder to.
The USERGROUP_ID he selected is 2.
The EVENT_ID (it's also the topic_id) of the event is 555.
So I need to retrieve all member ID's belonging to USERGROUP_ID = 2 from FORUM_USERGROUP_MEMBERS.
I need to remove those member ID's that have a matching record in FORUM_SIGNUP with an EVENT_ID = 555.
Then I need to get info from FORUM_MEMBERS on the remaining member ID's from the previous queries.
Anybody confused yet? 
The following is all I have of something of a query. Don't know if it makes sense or if I am way off.
strSql = "SELECT M.MEMBER_ID, M.M_NAME, M.M_EMAIL, T.T_SUBJECT "
strSql = strSql & "FROM " & strMemberTablePrefix & "MEMBERS AS M "
strSql = strSql & "WHERE M.MEMBER_ID NOT IN (SELECT UGM.MEMBER_ID FROM " & strTablePrefix & "USERGROUP_MEMBERS AS UGM OUTER JOIN " & strTablePrefix & "SIGNUP AS S ON UGM.MEMBER_ID = S.E_MEMBER_ID WHERE UGM.USERGROUP_ID = 2)"
Rui, I tried using your suggestions but don't know I am doing it right.  |
Support Snitz Forums
|
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 27 June 2005 : 19:58:24
|
I think this should do it. You didn't pay attention to my last couple messages in MSN...  .
strSql = "SELECT M.MEMBER_ID, M.M_NAME, M.M_EMAIL, T.T_SUBJECT "
strSql = strSql & "FROM " & strMemberTablePrefix & "MEMBERS AS M INNER JOIN " & strTablePrefix & "USERGROUP_MEMBERS AS UGM ON ON UGM.MEMBER_ID = M.MEMBER_ID) "
strSql = strSql & "WHERE M.MEMBER_ID NOT IN (SELECT S.E_MEMBER_ID FROM " & strMemberTablePrefix & " SIGNUP S WHERE S.EVENT_ID = 555) "
strSql = strSql & " AND UGM.USERGROUP_ID=2"
|
Snitz 3.4 Readme | Like the support? Support Snitz too |
Edited by - ruirib on 27 June 2005 19:58:52 |
|
|
|
Davio
Development Team Member
Jamaica
12217 Posts |
Posted - 28 June 2005 : 04:45:24
|
LoL!! I did Rui!! I diiiid!!! haha
Thanks again. How can I ever repay you? Sratch that idea. I'll think of something. |
Support Snitz Forums
|
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
| |
Topic |
|
Topic Locked
.