Probably simple LEFT JOIN question but I cant seem to get it to work

Is the ID field also in Table2?  Or is Field3 the only link between those two tables?

---

»bp

The ID field refers only to table one as to identify which record I want to show. Field3 will have the only common data between the two tables.

hi Mark,

 FROM table2 LEFT JOIN table1

means all records from table 2 will show -- and table 1 records will show only when they match

if you change Left to Right, it would be vice-verse

~~~
(table1.ID = ID)

this is ambiguous -- use
(table1.ID = table2.ID)

~~~
SELECT *

it is a good idea to specify what to get. If you want all fields from table2, make that clear

SELECT table2.*

have an awesome day,
crystal

Pls try this -

SELECT * FROM table1 LEFT JOIN table2 ON table2.field3 = table1.field3 
WHERE table1.ID = ID

Select allOpen in new window

Let me know if you are getting/not getting any records..??/

Your query should work, what are you seeing?

I'd probably do it the other way around though:

SELECT * FROM Table1 
LEFT JOIN Table2 ON Table2.Field3 = Table1.Field3 
WHERE (Table1.ID = varID);

Select allOpen in new window

---

»bp

I'd rename the ID variable to something else as well, very confusing to people and computers when variables and database column names are the same...

---

»bp

from my previous comments, the resulting SQL would be:

SELECT table2.*
FROM table2 LEFT JOIN table1
ON  table2.field3 = table1.field3
WHERE (table1.ID = table2.ID)

however, instead of table2.* perhaps you want to specify particular fields?

agree with Bill that ID is an ambiguous name ... and often means an AutoNumber which, of course, would not be a relevant match

if my last comment which below does not work try below one-

SELECT * FROM table1 LEFT JOIN table2 ON table2.field3 = table1.field3
WHERE table1.ID = ID

SELECT * FROM table1 FULL JOIN table2 ON table2.field3 = table1.field3
WHERE table1.ID = ID

I forgot to put in there what it returns, depending on how I do the join, I either get a record with all null values (ie left) or for the fun it, if change the JOIN type around, I get no records to display.

Try this - remove where clause and use FULL join and provide me the rows..

SELECT * FROM table1 FULL JOIN table2 ON table2.field3 = table1.field3

Mark, please read my comments about being specific about what you want after the SELECT keyword

I think your root problem is a variable with the name of the column in the table, change the variable to something like varID and then adjust the SQL to reference that and you should be good.

Demonstrated here that either will work...

http://rextester.com/XXCC62159

---

»bp

I would rather say , provide us data in excel with the expected output ..that shall help..

I agree with Pawan Kumar that some sample data for the two tables (in text format, not pictures) and your expected results would be of great help to the experts.

Could the problem be the case that Access cant perform FULL JOIN? I forgot to mention I'm dealing with Access and not SQL.

I don't think you want a FULL JOIN, you want the normal INNER JOIN.  You are only looking for the record from table2 that matches the table1 record, right?

---

»bp

yep.

Do you actually have a variable named ID, the same as the column name?  If so you need to change that...

---

»bp

No, ID is an autonumber field I just use to identify the record.

But when you say "WHERE table1.ID = ID" in your query, what is the second ID referring to?

---

»bp

There's a web page that links to this info page that basically shows the names of everyone in the database and I pass the ID variable to this info page so it pulls the information for the person whose name I clicked on. So the second ID should be referring to the record for the person I click on. I think the join is probably not the issue more so trying to get the correct record. If I leave off the where clause with the join, I get ALL the records from table2 no matter if field3 matches or not. With the where clause on there, it shows me an NULL record for table2. If I add some table1 fields, the where clause does grab the correct record and shows me correct info from table1 but table 2 fields still show null values when I know that there is a record in table1 and table2 for field3 that matches.

Sample data?

did you try this?

SELECT table2.*
FROM table2 LEFT JOIN table1
ON  table2.field3 = table1.field3
WHERE (table1.ID = table2.ID)

assuming ID is an AutoNumber in one of the tables and a Long Integer in the other

"I pass the ID variable to this info page"

So there is a variable named ID in the code, or parms, or something, is that what you mean when you say you pass it to the next form?

---

»bp

Where is the code running?  You allude to an Access database and a web page.  You also included SQL in your topic list.  If your database is Jet/ACE, you would need Access SQL syntax for the query.  If the database is something else, you may or may not need specific syntax for that RDBMS.  Access insulates you from having to learn the various SQL dialects if you are running your query in Access using an Access querydef which would use Access syntax regardless of where the linked table is actually located.  Try to tighten up your specifics.

If the query is running in Access, how you reference ID is still a question.  Are you referring to a control on an Access form or are you getting it some other way.  If the query is embedded SQL, then you would force Access to evaluate the ID to determine an actual value before sending the string to the database to be processed.  For example, in VBA, the code might be:

strSQL = "SELECT * FROM table2 LEFT JOIN table1 ON  table2.field3 = table1.field3 WHERE table1.ID = " & Me.ID

You would use something similar if you were running the query from a web page except that you would need to use whatever syntax the web page requires to concatenate the ID value with the rest of the SQL String.  The objective is to end up with a string that looks like:

SELECT * FROM table2 LEFT JOIN table1 ON  table2.field3 = table1.field3 WHERE table1.ID = 12345

And that is what is going to the query engine for processing.

The answer is in the thread ... read all the posts. it is easy to get overwhelmed with lots of "try this" and "try that"  -- some of the posts explain what is happening with the SQL statement and how to fix it, provided you want to learn

1) Where is the data stored?
2) Where is the code running?

Start with those two.

Try it this way

strSQL = "SELECT * FROM table2 LEFT JOIN table1 ON  table2.field3 = table1.field3 AND table1.ID = " & Me.ID

SO I didnt give up and here's what I was able to get to work:

SELECT * FROM Table1 INNER JOIN Table2 ON Table1.Field3 = Table2.Field3 WHERE INV.ID = ::ID::

BUT the real problem was for some reason, the Access database was not recognizing the data in Table2.Field3. I ran a simple select. SELECT Field3 FROM Table2 WHERE Field3 = 'MyData' and it came back with no records found. I went to the table itself, deleted the contents completely and pasted the value back in and it showed up. There was not leading spaces or errant character that I could tell initially bit it seems the JOIN statements were probably working all along, Just a problem with the data.

I dont know enough to answer the experts questions.