Left join doesn't return all expected records

  • Thread starter Thread starter Yakimoto
  • Start date Start date
Y

Yakimoto

Hi folks,
I have a problem with the left join...

I have 2 tables. The left table V contains the IDs, and the next table K
contains the data.
V ID Code
1 01
2 02
7 07


K Date VID Kilometers
7/20/2003 1 550
7/21/2003 7 777


I would like to select all the records from table V and to get the
kilometers for them from table K. If I do not specify the Date in where
clause, everything is perfect with my LEFT JOIN.
But if I specify particular date 7/20/2003 in where clause, I get only one
record as a result (ID=1).
if I specify
WHERE (((K.Date)=#7/20/2003# Or (K.Date) Is Null))
then I get 2 records (ID=1 and ID=2), but not ID=7.
Here is my SQL:
SELECT V.ID, K.Kilometers, K.Date
FROM V LEFT JOIN K ON V.ID = K.VID
WHERE (((K.Date)=#7/20/2003# Or (K.Date) Is Null))

and here is the result:
ID Kilometers Date
1 550 7/20/2003
2



I would like to have one more record, containing ID=7, but wit 0/null km,
because there is no record for this ID for that date.
Shouldn't my LEFT JOIN return all the records from my left table?
Where am I wrong?

Cheers,
Yakimoto
 
Access 2000 or later, you might try

SELECT V.ID, tmp.Kilometers, tmp.[Date]
FROM V LEFT JOIN
(SELECT K.* FROM K WHERE K.[Date] = #7/20/2003#) as Tmp
ON V.ID = tmp.VID

You could also do this in two queries.
QueryA
SELECT K.* FROM K WHERE K.[Date] = #7/20/2003#

QueryB (using query A)
SELECT V.ID, QueryA.Kilometers, QueryA.[Date]
FROM V LEFT JOIN QueryA
 
FWIW, you can also do it in 97, but like this:

SELECT V.ID, tmp.Kilometers, tmp.[Date]
FROM V LEFT JOIN
[SELECT K.* FROM K WHERE K.[Date] = #7/20/2003#]. as Tmp
ON V.ID = tmp.VID

But it might not work in any version because of the required brackets around
the field name that is a reserved word (Date).

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out" (coming soon)
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
John Spencer (MVP) said:
Access 2000 or later, you might try

SELECT V.ID, tmp.Kilometers, tmp.[Date]
FROM V LEFT JOIN
(SELECT K.* FROM K WHERE K.[Date] = #7/20/2003#) as Tmp
ON V.ID = tmp.VID

You could also do this in two queries.
QueryA
SELECT K.* FROM K WHERE K.[Date] = #7/20/2003#

QueryB (using query A)
SELECT V.ID, QueryA.Kilometers, QueryA.[Date]
FROM V LEFT JOIN QueryA
Hi folks,
I have a problem with the left join...

I have 2 tables. The left table V contains the IDs, and the next table K
contains the data.
V ID Code
1 01
2 02
7 07

K Date VID Kilometers
7/20/2003 1 550
7/21/2003 7 777

I would like to select all the records from table V and to get the
kilometers for them from table K. If I do not specify the Date in where
clause, everything is perfect with my LEFT JOIN.
But if I specify particular date 7/20/2003 in where clause, I get only one
record as a result (ID=1).
if I specify
WHERE (((K.Date)=#7/20/2003# Or (K.Date) Is Null))
then I get 2 records (ID=1 and ID=2), but not ID=7.
Here is my SQL:
SELECT V.ID, K.Kilometers, K.Date
FROM V LEFT JOIN K ON V.ID = K.VID
WHERE (((K.Date)=#7/20/2003# Or (K.Date) Is Null))

and here is the result:
ID Kilometers Date
1 550 7/20/2003
2

I would like to have one more record, containing ID=7, but wit 0/null km,
because there is no record for this ID for that date.
Shouldn't my LEFT JOIN return all the records from my left table?
Where am I wrong?

Cheers,
Yakimoto
Click to expand...
Click to expand...
 
Thanks a lot, that's exactly what I needed.

Yakimoto

John Viescas said:
FWIW, you can also do it in 97, but like this:

SELECT V.ID, tmp.Kilometers, tmp.[Date]
FROM V LEFT JOIN
[SELECT K.* FROM K WHERE K.[Date] = #7/20/2003#]. as Tmp
ON V.ID = tmp.VID

But it might not work in any version because of the required brackets around
the field name that is a reserved word (Date).

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out" (coming soon)
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
John Spencer (MVP) said:
Access 2000 or later, you might try

SELECT V.ID, tmp.Kilometers, tmp.[Date]
FROM V LEFT JOIN
(SELECT K.* FROM K WHERE K.[Date] = #7/20/2003#) as Tmp
ON V.ID = tmp.VID

You could also do this in two queries.
QueryA
SELECT K.* FROM K WHERE K.[Date] = #7/20/2003#

QueryB (using query A)
SELECT V.ID, QueryA.Kilometers, QueryA.[Date]
FROM V LEFT JOIN QueryA
Hi folks,
I have a problem with the left join...

I have 2 tables. The left table V contains the IDs, and the next table K
contains the data.
V ID Code
1 01
2 02
7 07

K Date VID Kilometers
7/20/2003 1 550
7/21/2003 7 777

I would like to select all the records from table V and to get the
kilometers for them from table K. If I do not specify the Date in where
clause, everything is perfect with my LEFT JOIN.
But if I specify particular date 7/20/2003 in where clause, I get only one
record as a result (ID=1).
if I specify
WHERE (((K.Date)=#7/20/2003# Or (K.Date) Is Null))
then I get 2 records (ID=1 and ID=2), but not ID=7.
Here is my SQL:
SELECT V.ID, K.Kilometers, K.Date
FROM V LEFT JOIN K ON V.ID = K.VID
WHERE (((K.Date)=#7/20/2003# Or (K.Date) Is Null))

and here is the result:
ID Kilometers Date
1 550 7/20/2003
2

I would like to have one more record, containing ID=7, but wit 0/null km,
because there is no record for this ID for that date.
Shouldn't my LEFT JOIN return all the records from my left table?
Where am I wrong?

Cheers,
Yakimoto
Click to expand...
Click to expand...
Click to expand...
 
Back
Top