Select Where data AND data AND,,,

Anon

I hope that someone can help with this one. I have racked my brain.

I have three tables and a
$caller = 123;
|-------------------------------------------|
| Phone table |
|-------------------------------------------|
|line#|cust#|type|phone#|last-called|balance|
|-------------------------------------------|

|-------------------------------------------|
| Transaction table |
|-------------------------------------------|
| line# | cust# | type | caller | date |
|---------------- --------------------------|

|----------------------------|
| Report table |
|----------------------------|
| line# | cust# | caller |
|----------------------------|

What I have to do is select a phone number for caller to call based on these factors.

The customer has to have a balance.
The customer can not have been called within the last week by this caller for this type of account.
The caller can not have the cust# on their report list.

The way I see it the first part of the where should be

WHERE Phone.cust# ! = Report.cust# AND Report.caller ! = $caller

This will take care of the first part of the query. Question is how do I check the second table of data to make sure that this caller can have this phone number to call?

Thanks in advance to anyone who can help.

esolis

This involve using sub-selects. You didn't mention what database product you're using so I don't know if it supports sub-select.

Anyway, it goes something like this...

SELECT * FROM table WHERE caller NOT IN (SELECT caller FROM report_table)

or for those that don't support sub-selects you might be able to do something like this...

SELECT table.* FROM table LEFT JOIN report_table ON table.caller=report_table.caller WHERE report_table.caller IS NULL

This will only handle the third condition. I will post again in a few hours.

esolis

OK... Upon further reading, I have to revise my previous posting to as follows.

SELECT * FROM phone WHERE phone.cust# NOT IN (SELECT cust# FROM report WHERE report.caller=$caller) AND phone.balance > 0

This will take care of conditions 1 and 3. I will post again in a couple of hours.

Anon

Thanks Ed

I am using a mysql database.

not sure about the sub selects but I will go check.

esolis

MySQL doesn't support sub-selects yet.

SELECT * FROM phone WHERE phone.cust# NOT IN (SELECT cust# FROM report WHERE report.caller=$caller) AND phone.balance > 0

can be converted to...

SELECT * FROM phone LEFT JOIN report ON table.cust#=report.cust# WHERE report.cust# IS NULL AND report.caller=$caller AND phone.balance > 0

I'm still working on the 2nd condition.