×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Sql Query Join problem with multiple column types

Sql Query Join problem with multiple column types

Sql Query Join problem with multiple column types

(OP)
Hi, I have two tables that I am trying to extract information from and am having trouble getting it.  I need to create a report that shows a list of users who have not responded to any particular notice that is sent to them.  Confirmation reminders go out every seven days until they respond.  The tables look like this:

Table 1, Table 2

Table1 contains notice information such as notice name, notice number, etc.
Table2 contains Recipient information such as user Name.  It also contains Notice Status and Notice Type.


The problem is that there are multiple notice types such as "Confirmation Reminder" and "Original Notice."  Once a notice is sent to a user, a db record is created in Table2 that shows the date the notice was sent, the status as SENT, and the notice type as "Original Notice."  Once they respond to that notice, another record is created in Table2 showing the status as Accepted and the notice type, again, as "Original Notice."  

If they do not respond, a confirmation notice is sent to them and a db record is created in Table2 with a status of SENT, and the type as "Confirmation Reminder."

My query has to pull up all the records where the notice type = "Confirmation Reminder," but I only need the ones where the "Original Notice" not yet been 'Accepted' AND (this is the kicker) I need the Confirmation Reminder SENT DATE as part of the result set.  This date is located in Table2.  As it is, I can either pull up records where the Notice Type = "Original Notice" AND Notice Status = "Sent" or "Accepted."  OR I can pull up Notice type = "Confirmation Reminder" and Status = "Sent."  There are no records where "Confirmation Reminder" = "Accepted" or this would have been easier to do.

Hopefully I have given enough information for someone to help me.  Thank you very much

RE: Sql Query Join problem with multiple column types

Hello.

You can use a table as link of itself:

SELECT a.field1, b.field1, c.field1
  FROM table1 a, table1 b, table1 c
 WHERE a.id = b.id
   AND a.id = c.id
ORDER BY 1,2,3

Table1 is always the same and field also, but used as different tables. Now you can use the WHERE clause to extract different data from the same source.

If you need more info, please post some data and desired result, and we'll help you.

JOSE LUIS.

RE: Sql Query Join problem with multiple column types

(OP)
Thank you very much, Jose!  I will try this.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close