finding the greatest match

[stubarny]

Hello,

 

I have a query that finds details of job searches made on my (recruitment) site.

 

It currently returns all matches on "a.research_job_search_index=b.research_job_search_history_research_job_search_index" that are more than 24hrs old.

 

Please could you tell me how to change this query so that it returns only the most recent match for each set of matches of "a.research_job_search_index=b.research_job_search_history_research_job_search_index" that are more than 24hrs old?

 

$query = "SELECT * FROM research_job_searches a LEFT OUTER JOIN research_job_searches_history b 
ON 
a.research_job_search_index=b.research_job_search_history_research_job_search_index 
WHERE 
b.research_job_search_history_timestamp < $timestamp_1_day_ago";

 

Many thanks for your help,

 

Stu

[fenway]

See here.

[stubarny]

Hi fenway,

 

Many thanks. I'm having some trouble combining your previous solution (a self-join) with my query. Here's where I've got to:

 

$query = "SELECT * FROM research_job_searches a LEFT OUTER JOIN research_job_searches_history b 
ON 
(
a.research_job_search_index=b.research_job_search_history_research_job_search_index 
AND 
a.research_job_search_history_timestamp < b.research_job_search_history_timestamp
) 
WHERE 
b.research_job_search_history_timestamp is null";

 

The problem is that a.research_job_search_history_timestamp is not a field in my table a.

 

Please could you show me how to integrate your self-join query within my query?

 

Many thanks,

 

Stu

[stubarny]

Hi fenway,

 

Thanks for your help - I appear to have fixed it but have just 1 more question    Instead of selecting all columns using "*", I'd like to select all columns from table alias b - please could you tell me if this is possible?

 

$query = "SELECT * FROM research_job_searches a 
LEFT OUTER JOIN research_job_searches_history b 
ON 
(a.research_job_search_index = b.research_job_search_history_research_job_search_index 
AND 
b.research_job_search_history_timestamp < $timestamp_1_day_ago
) 
LEFT OUTER JOIN 
research_job_searches_history c 
ON 
( 
(a.research_job_search_index = c.research_job_search_history_research_job_search_index)
AND 
b.research_job_search_history_timestamp < c.research_job_search_history_timestamp 
) 
WHERE c.research_job_search_history_timestamp IS NULL";

 

Many thanks for your help - you saved me a lot of time today! 

 

Stu

[fenway]

So, b.*?