Inner join issue

[evergreean]

Please advise how I can fetch info on my table join that I am using for search information for my Access 2000 db.
If a field search hit for TableOne has the data and the other table has the foreign key for that hit it will give me a hit on the TableOne data. But if a field search hit for TableOne has a hit and there is no foreign key for that hit in TableTwo then I get no results found.

For example if my Search looks for the word "Joe" it will give me a hit on it because the foreign key exists in TableTwo. But if I search for the word "Smith" it will not give me a hit because TableTwo doesnt have a foreign key value for it.

Tables:

TableOne

myid   info
1    Smith area
2    Joe left
3    data here


TableTwo
id   myid  details
1    2     more stuff
2    2     words and words

My inner join:

SELECT *
FROM tableOne
INNER JOIN tableTwo 
ON tableOne.myid = tableTwo.myid
where tableOne.info = "Smith";

Please advise.

 

[PHV]

And what about replacing INNER with LEFT ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[evergreean]

Thanks, that worked.

Left join says match every hit on TableOne whether or not you have hit on TableTwo? Then Right join matches every hit on TableTwo whether or not you have hit on TableOne?

 

[lespaul]

check out Understanding SQL Joins

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design