outer join is not working

  • Thread starter Thread starter sheela
  • Start date Start date
S

sheela

I have Acess 2002 vesrion. I am having problem with outer
join. I am querying from two tables. The table1 has only
one field with values from 1 through 81. (81 rows)
It is a parameter query, once the user gives the correct
values to the query prompts, the query will result only
one record from table2.
But I need the results in 81 rows, (all the rows, from
table1, null values for the table 2 fields, for the
remaining records).
I am using the following query. This query should result
81 records, but it is giving only one record. For some
reason it is using inner join, not outer join.
What am I doing wrong here? I appreciate the help ASAP.


SELECT table1.PositionNum, table2.Field1, table2.Position
FROM Table1 LEFT JOIN table2 ON Table1.PositionNum =
table2.Position
WHERE (((table2.Field1)=[Please enter the ..]));

TIA,
Sheela
 
sheela said:
I have Acess 2002 vesrion. I am having problem with outer
join. I am querying from two tables. The table1 has only
one field with values from 1 through 81. (81 rows)
It is a parameter query, once the user gives the correct
values to the query prompts, the query will result only
one record from table2.
But I need the results in 81 rows, (all the rows, from
table1, null values for the table 2 fields, for the
remaining records).
I am using the following query. This query should result
81 records, but it is giving only one record. For some
reason it is using inner join, not outer join.
What am I doing wrong here? I appreciate the help ASAP.


SELECT table1.PositionNum, table2.Field1, table2.Position
FROM Table1 LEFT JOIN table2 ON Table1.PositionNum =
table2.Position
WHERE (((table2.Field1)=[Please enter the ..]));

TIA,
Sheela
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You're asking for specific records in Table2. If there is only 1 record
in table2 that fits your criteria then only one record will be returned
by the query. If you want all 81 records from Table1 your criteria
should be for column values in Table2 that don't match the join column
values in Table1 (1), or have an OR clause for Table2 that includes the
possibility that the criteria for a record will fail (2).

(1) WHERE table2.Position Is Null
[since your joining on Position/PositionNum]

(2) WHERE table2.Field1=[Please enter the ..]
OR table2.Field1 Is Null

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQIAz3oechKqOuFEgEQLMmgCg931P+YanUVDKek5thIZjRbTwZb4AnioG
pOl4ACC3UhrrvKfGM8aSNjxO
=4n3/
-----END PGP SIGNATURE-----
 
It works, thanks so much
-----Original Message-----
sheela said:
I have Acess 2002 vesrion. I am having problem with outer
join. I am querying from two tables. The table1 has only
one field with values from 1 through 81. (81 rows)
It is a parameter query, once the user gives the correct
values to the query prompts, the query will result only
one record from table2.
But I need the results in 81 rows, (all the rows, from
table1, null values for the table 2 fields, for the
remaining records).
I am using the following query. This query should result
81 records, but it is giving only one record. For some
reason it is using inner join, not outer join.
What am I doing wrong here? I appreciate the help ASAP.


SELECT table1.PositionNum, table2.Field1, table2.Position
FROM Table1 LEFT JOIN table2 ON Table1.PositionNum =
table2.Position
WHERE (((table2.Field1)=[Please enter the ..]));

TIA,
Sheela
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You're asking for specific records in Table2. If there is only 1 record
in table2 that fits your criteria then only one record will be returned
by the query. If you want all 81 records from Table1 your criteria
should be for column values in Table2 that don't match the join column
values in Table1 (1), or have an OR clause for Table2 that includes the
possibility that the criteria for a record will fail (2).

(1) WHERE table2.Position Is Null
[since your joining on Position/PositionNum]

(2) WHERE table2.Field1=[Please enter the ..]
OR table2.Field1 Is Null

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQIAz3oechKqOuFEgEQLMmgCg931P+YanUVDKek5thIZjRbTwZb 4AnioG
pOl4ACC3UhrrvKfGM8aSNjxO
=4n3/
-----END PGP SIGNATURE-----

.
Click to expand...
 
Back
Top