sql experts, I've got a question...

say I have Table1(id, date, n)
and Table2(id, n, value)

I want to return all records with a given id, where Table1.n = Table2.n, and there may or may not be a Table2 record for every (id, n) pair.

I tried:

SELECT * FROM Table1 LEFT JOIN Table2 ON Table1.n=Table2.n WHERE Table1.id=Table2.id AND Table1.id=##

but it's only returning records which have an (id, n) pair in Table2. I want everything in Table1, whether or not the given id and n-value appear in Table2...

I also tried changing it to ON Table1.id=Table2.id, but that returns everything from Table1 once for every record in Table2.

am I doing something wrong here?

LEFT OUTER JOIN is what you want (or whatever the syntax is in your dialect)

tried that too, but the same results.

e.g. I have:



so I want every record in Table1, with an added field "value" in the output with the records in Table2 that match up. so I want a query to produce:



am I correct in joining on Table1.n=Table2.n?

Since you are joining on both ID AND n, you have to outer join both of them.
Here's what Access SQL says:

SELECT Table1.id, Table1.date, Table1.n, Table2.value
FROM Table1 LEFT JOIN Table2 ON (Table1.n = Table2.n) AND (Table1.id = Table2.id);

thanks guys, looks like both of you were right, and I had the mistake - I had a WHERE Table1.id=Table2.id clause. when I took that out, I got the results I wanted.

Is this what you want?


edit: looks like I was late to the party