How do I combine these two queries correctly?

[webmaster1]

I have two tables:

 

 

I have an interface that displays one record from a table at a time. The selection process is automatic and based on the following conditions:

 

It must have the soonest record.closedate:

 

SELECT *
FROM record
WHERE agent = 'Bob Dole' /*only records assigned to current end-user*/
AND life = 'active' /*only records that are 'turned-on'*/
order by closedate asc
LIMIT 0,1

 

If a record has corresponding notations, select the notation with the most recent notations.inputdate (but if it doesn’t satisfy the where conditions, exlude it).

 

SELECT * 
FROM record JOIN notations 
ON record.id = notations.recordid
WHERE notations.result =  'Non-connect' /*but only those where notations.inputdate > 24hrs from current time*/
AND (opportunities.stage <>  '08' OR opportunities.stage <>  '09')
ORDER BY notations.inputdate desc

 

My obvious problem is that I don’t know how to combine or nest the above queries.

 

My second problem is that I don’t know how to limit the first WHERE condition of the second query to records with an notations.inputdate > 24hrs from current time.

 

Any guidance or suggestions would be greatly appreciated.

 

[webmaster1]

My second problem can obviously be solved as follows:

 

SELECT recordid, result, MAX( inputdate ) as latestinputdate
FROM notations
GROUP BY recordid
ORDER BY latestinputdate

 

I just can't get my head around bringing the whole query together. My minds wants to do an if/else statement. 

 

 

[fenway]

Start thinking in sets, not procedures.

[webmaster1]

Start thinking in sets, not procedures.

 

Appreciated. Looking into unions now.

[ignace]

AND (opportunities.stage <>  '08' ANDOR opportunities.stage <>  '09')

[webmaster1]

AND (opportunities.stage <>  '08' ANDOR opportunities.stage <>  '09')

 

Cheers for that.

[webmaster1]

I've gone about the UNION approach as suggested by fenway:

 

SELECT id as id FROM record 
WHERE agent = 'Bob Dole' 
AND life = 'active' 
AND (stage <>  '08' AND stage <>  '09')

UNION

SELECT recordid as id FROM notations
/*WHERE result = 'Non-connect'*/

 

The query as it stands returns 100 rows.

 

When I include the commented where statement I still end up with 100 rows. I should only have 14.

 

Why doesn't the WHERE condition have an effect? It works by as single query.

 

 

[fenway]

I don't follow.

[webmaster1]

The WHERE condition of the second query doesn't filter the 100 rows to 14 rows.

 

100 rows represents total rows

14 rows represents result = 'Non-connect'

 

The WHERE conditions work fine on the first query in the UNION by filtering 400 records to 100 records.

The WHERE condition has no effect on the second query.

[fenway]

You mean to say that the second query alone doesn't produce 14 records?  If it does, you need a join, not a union.  I'm confused, yet again.

[webmaster1]

I've finally got the query to work. I tried sub-queries and sets/union but in the end the left join done the trick.

 

Rather than using max and min on the two datetime fields, I simply used the order by.

 

Any tips/criticism welcome.

 

SELECT 
id,
agent, 
record.life,
contactname,
contactemail,
contactphone,
recordid,
/*max(inputdate) as maxinputdate*/
/*min(renewalclosedate) as minclosedate*/

FROM record LEFT JOIN notations 
ON record.id = notations.recordid

WHERE (notations.result IS NULL OR notations.result= 'Non-connect')
AND (record.agent = 'Bob Dole' AND record.life = 'active')
AND (record.stage <>  '08' AND record.stage <>  '09')
group by recordid
order by closedate asc, inputdate desc
LIMIT 0,1

 

What a nightmare for something so simple! 

 

Note to self: Write out what the query is doing in plain English before coding.

 

 

[webmaster1]

You mean to say that the second query alone doesn't produce 14 records?  If it does, you need a join, not a union.  I'm confused, yet again.

 

Sorry for the confusion. Aside from very basic select and insert statements, I haven't touched sql in good while so my explanation was probably as screwy as my querying. The LEFT JOIN done the trick. Cheers!