?
Solved

Multiple Table Query

Posted on 2003-03-12
10
Medium Priority
?
224 Views
Last Modified: 2007-12-19
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
Comment
Question by:bugs021997
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 
LVL 41

Accepted Solution

by:
shanesuebsahakarn earned 1000 total points
ID: 8121924
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
 
LVL 14

Author Comment

by:bugs021997
ID: 8129840
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
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 8130008
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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 14

Author Comment

by:bugs021997
ID: 8130192
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
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 8130255
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
 
LVL 14

Author Comment

by:bugs021997
ID: 8134334
How to run queries within access as i m new to access cause i use oracle sql and mysql
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 8134869
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
 
LVL 12

Expert Comment

by:nexusnation
ID: 8785618
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
 
LVL 18

Expert Comment

by:1William
ID: 8889299
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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question