Joins

[Vasanth79]

Hello all:

I am trying to implement Automatic emailing sytem in our program. Basically, we want to send an email to our customers when specific event occurs. Events could be delay in delivery time by 30 mins, 60 mins and so. At the same time, I don't want to send 30 min delay email to a customer who was already notified so we register the event and the customer info in a table every time we send an email. I am using the following five tables for the emailing system.

Table A have AccountID and CustomerID. (Customer Info table)
values
--------
1,a
2,b

Table B have EventID, Event (Event info table)
values
--------
1,30
2,60

Table C have EmailID, Email (Email Info Table)
values
--------
1, [e-mail]
2,[e-mail]

Table D have AccountID,EmailID and EventID (Acct-Email-Event Link Table)
values
--------
1,1,1
1,1,2
1,2,1
1,2,2
2,1,1
2,1,2
2,2,1
2,2,2

Table E have CustomerID,EventID (Email Sent Info Table)
values
--------
a,1

I want to write a query that gives me the eventid and emailid of customers when a new event occurs. For instance, let us assume there are two customers A and B. Customer A's order gets delayed by 30 mins. The emailing sytem will send a 30 min delay email and will register the Event and the CustomerID in table E. Now, when Customer B's order gets delayed by 30 mins, I have to check table E to ensure that 30 min email is not already sent to that customer. In order to do this task, I wrote the following query, but it is not working for some reason.

Select A.CustomerID, C.Email,B.Event
from Table A
JOIN Table D on D.AccountID = A.AccountID
JOIN Table B on B.EVENTID = D.EVENTID
JOIN Table C on C.EmailID = D.EmailID
JOIN TABLE E on (E.CustomerID <> A.CustomerID and E.EVENTID <> B.EVENTID )

The results of the above query are
a,1,2
b,1,2

But, it needs to have the following in its result set.
b,1,1

Any help to solve the problem will be greatly appreciated.

Thanks

[r035198x]

Hello all:

I am trying to implement Automatic emailing sytem in our program. Basically, we want to send an email to our customers when specific event occurs. Events could be delay in delivery time by 30 mins, 60 mins and so. At the same time, I don't want to send 30 min delay email to a customer who was already notified so we register the event and the customer info in a table every time we send an email. I am using the following five tables for the emailing system.

Table A have AccountID and CustomerID. (Customer Info table)
values
--------
1,a
2,b

Table B have EventID, Event (Event info table)
values
--------
1,30
2,60

Table C have EmailID, Email (Email Info Table)
values
--------
1, [e-mail]
2,[e-mail]

Table D have AccountID,EmailID and EventID (Acct-Email-Event Link Table)
values
--------
1,1,1
1,1,2
1,2,1
1,2,2
2,1,1
2,1,2
2,2,1
2,2,2

Table E have CustomerID,EventID (Email Sent Info Table)
values
--------
a,1

I want to write a query that gives me the eventid and emailid of customers when a new event occurs. For instance, let us assume there are two customers A and B. Customer A's order gets delayed by 30 mins. The emailing sytem will send a 30 min delay email and will register the Event and the CustomerID in table E. Now, when Customer B's order gets delayed by 30 mins, I have to check table E to ensure that 30 min email is not already sent to that customer. In order to do this task, I wrote the following query, but it is not working for some reason.

Select A.CustomerID, C.Email,B.Event
from Table A
JOIN Table D on D.AccountID = A.AccountID
JOIN Table B on B.EVENTID = D.EVENTID
JOIN Table C on C.EmailID = D.EmailID
JOIN TABLE E on (E.CustomerID <> A.CustomerID and E.EVENTID <> B.EVENTID )

The results of the above query are
a,1,2
b,1,2

But, it needs to have the following in its result set.
b,1,1

Any help to solve the problem will be greatly appreciated.

Thanks

I don't see where all these joins come in.
not exists is tailor made for this.
However if these tables are not temporary, you may want to add a date field. i.e we may delay deliveries many times for the same customers.

Edit: I suppose you are using triggers for this.

[Vasanth79]

I am working in an Interbase database. I am assuming MYSQL has JOINS similar to Interbase. Any how I figured out the solution myself.

Thanks