INNER JOIN statement

[stubarny]

Hello,

 

This code below selects all the records in table "research_job_searches" that have corresponding records in table "research_job_searches_history".

 

$query = "SELECT * FROM research_job_searches 
INNER JOIN 
research_job_searches_history 
ON 
research_job_searches.research_job_search_index=research_job_searches_history.research_job_search_history_index";

 

Please could you tell me how to re-write this code so that it selects all the records in table "research_job_searches" that do not have corresponding records in table "research_job_searches_history"?

 

Thanks,

 

Stu

[Zane]

You would use a LEFT OUTER JOIN for this, with a WHERE clause.

 

Also, it wouldn't hurt to assign aliases for easier coding.

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_index
WHERE b.research_job_search_history_index IS NULL

 

Here's a great tutorial on JOINs, with pictures!

http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html

[stubarny]

Wow, thanks Zane that link cleared up 2 years of confusion!

 

How would I limit this to return no more than one (joined) record?

[fenway]

How would I limit this to return no more than one (joined) record?

That doesn't exist.  Set theory doesn't allow for "one of".Which one would you want? Based on that, you can use a self-join to limit that result to a single record, and then you're golden. 

[stubarny]

Hi fenway,

 

sorry I'm lost - I thought a self-join statement is used on two copies of the same table? how would I apply a self-join statement to Zane's code?

 

I just need 1 matching record to be returned (it doesn't matter which matching record is returned)

 

Thanks,

 

Stu

[fenway]

You'd use a self-join to find the record you want -- then join the result back to whatever other tables(s) you need.

[stubarny]

Hi Fenway,

 

I would agree with you if I was trying to return a record from research_job_searches_history, but I'm trying to return a record that does not exist in research_job_searches_history. Therefore surely I can't perform a select operation without referring to the other table (research_job_searches_history)?

 

Stu

[fenway]

Sorry, I missed that somehow -- then you want " <> ANY ">