• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 226
  • Last Modified:

Multiple Table Query

I am selecting the mails for a particular user(one who logs in).But if the user has specified that someone@someone.com is in my blocked list then that mail should not be display and the mail should be blocked.

I have three tables Users,Messages and Block now i have used the three tables to write a query to display the messages excluding the blocked list but none of them is working and i am getting and EXCEPTION ERROR.help me its urgent

SQLmsg = "Select Messages.toAdd,Messages.read, Messages.msgId, Messages.message, Messages.dateSent, Messages.subject, Messages.fromAdd, Block.username"

SQLmsg = SQLmsg & " From Block Inner Join Messages On Block.username = Messages.toAdd"
SQLmsg = SQLmsg & " Where toAdd='"&session("username")&"' And Messages.toAdd Not In Block.blockedEmails"

SQLmsg = "SELECT Messages.toAdd, Messages.read, Messages.msgId, Messages.message, Messages.dateSent, Messages.subject, Messages.fromAdd, Block.blockedEmails FROM Messages LEFT JOIN Block ON Messages.fromAdd=Block.username"

SQLmsg = "Select Messages.toAdd, Messages.read, Messages.msgId, Messages.message, Messages.dateSent, Messages.subject, Messages.fromAdd, Block.blockedEmails From Messages Join Block On Messages.msgId = Block.userId Join Users On Block.userId = Users.userId"


SQLName = "Select [u.firstName], [u.lastName], [m.fromAdd], from [Users] u, [Messages] m where [m.fromAdd]=[u.username]"
0
bugs021997
Asked:
bugs021997
1 Solution
 
shanesuebsahakarnCommented:
Can you describe your table structure and which field is stored where ?

You might use a query along the lines of:
"SELECT Users.*, Messages.* FROM Users INNER JOIN Messages ON Users.Username = Messages.Username WHERE Messages.FromAdd NOT IN (SELECT blockEMails FROM Block WHERE Block.Username = Users.Username)

Without knowing the structure, it's difficult to be exact about the SQL.
0
 
bugs021997Author Commented:
Messages Table :

msgId  //Stores the auto number value
fromAdd //Stores the email id of the person who sent the mail
toAdd       //Stores the email id to who the mail has been sent
subject //Stores the subject
message //Stores the message
read     //Stores whether the mail is read/unread
dateSent // Stores the date on which the mail was sent


Users Table :
userId      //Stores the auto number value
username //Stores the email id of the person who registers
      :
      :
rest all that a register form normally has like sex,education,age etc etc

Block Table :  
userId     //Stores the auto number value
username //Stores the email id of the person who registered
blockedEmails //Stores the email id of the person who has to be blocked

Now what I want is when I am showing the mail received by a particular user who registered, all those mails from a person whose email id is not present in the table named BLOCK against the username of the person who has logged in should be displayed.

Or if there is some problem in my database then why don’t u suggest me some changes without making any major changes.
0
 
shanesuebsahakarnCommented:
Is the email id (ie the blockedEmails field) the same information as the toAdd fromAdd field ? If so, something like this should work:
"SELECT Users.*, Messages.* FROM Users INNER JOIN Messages ON Users.Username = Messages.toAdd WHERE Messages.FromAdd NOT IN (SELECT blockEMails FROM Block WHERE Block.Username = Users.Username)
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
bugs021997Author Commented:
No blockedEmails field in the Block Table and fromAdd field from the Messages table are the same.And the code u gave me , i tried but its stile giving me error in the asp page like ExceptionError 00*0800004005
0
 
shanesuebsahakarnCommented:
Well, the error in question seems to indicate a permissions problem.

Have you tried the query within Access itself to see if it runs properly ?
0
 
bugs021997Author Commented:
How to run queries within access as i m new to access cause i use oracle sql and mysql
0
 
shanesuebsahakarnCommented:
Open Access, then click Queries. Design a new query (don't add any tables). Click View->SQL View and paste the SQL into there. Then click the Run icon (the ! icon), and see if the query works - it should give you a more descriptive error message if it fails.
0
 
nexusnationCommented:
Hi bugs,
This question has been abandoned and needs to be finalized (101 days since last comment).

   You can accept an answer, split the points, or get a refund. Go to
   http://www.cityofangels.com/Experts/Closing.htm for information and options.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

nexusnation
EE Cleanup Volunteer for Microsoft Access
0
 
1WilliamCommented:
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
Accept question, points to shanesuebsahakarn
Please leave any comments here within the next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

1William
EE Cleanup Volunteer
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now