LEFT JOIN Woes (Part 2)

[Don]

In an earlier post I was having problems with a LEFT JOIN not including left
table records when there were no associated right table records. The
problem being in the WHERE clause:

WHERE P.PositionLocation IN ( strPositionLocation )
AND R.PersonStatus In (strPersonStatus)

John Spenser suggested modifying the where clause to:

WHERE P.PositionLocation IN ( strPositionLocation )
AND ((R.PersonStatus IS Null) OR (R.PersonStatus In (strPersonStatus))


which did the trick. Or so I thought. While it picks up the cases where a
left record has no associated right table records, I noticed the query
result lacks results where a left record has an associated right table
record, but that right table record does not satisfy the IN clause.

Here is a simple example of what I am trying to achieve:

tblLeft
ID Shape Dimensions
1 Line 2
2 Circle 2
3 Sphere 3
4 Cube 3
5 Square 2
6 Cone 3
7 Triangle 2
8 Tube 3
9 Dot 1


tblRight
Product LeftID Color
A 1 Red
B 1 Green
C 2 Blue
D 3 Green
E 5 Red



Query Result for
.....
FROM tblLeft AS L LEFT JOIN tblRight AS R ON L.ID = R.LeftID
WHERE L.Dimensions IN (1, 2) AND ((R.Color IS Null) OR (R.Color IN ("Green",
"Blue")))

Yields:
ID Shape Dimensions Product Color
1 Line 2 B
Green
2 Circle 2 C
Blue
3 Sphere 3
4 Cube 3
5 Square 2
6 Cone 3
7 Triangle 2
8 Tube 3
9 Dot 1

The "problem children" being like Product "E" (right table) that meet all
the criteria except color. In the actual result for the query - based on my
observations - ID 9 would not be included in the query.


This is where my SQL skills really break down (being a novice, that does not
take much!). My guess is that I really want a subquery to get only those
"right" records that match the "right" criteria I want. These results would
then be the right side of the LEFT JOIN clause.

Any thoughts (other than I'm crazy )?!

Thanks!

Don

 

[MGFoster]

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

ID 9 should be included in the query, 'cuz it satisfies the criteria
"Dimensions In (1,2)."

When I ran your query I got a different result set:

ID Shape Dimensions Product Color
1 Line 2 B Green
2 Circle 2 C Blue
7 Triangle 2
9 Dot 1

If you want to eliminate the ID 9 line, change the criteria like this:

WHERE L.Dimensions IN (1, 2)
AND (R.Color IS NOT NULL OR R.Color IN ('Green','Blue'))

The result set looks like this:

ID Shape Dimensions Product Color
1 Line 2 A Red
1 Line 2 B Green
2 Circle 2 C Blue
5 Square 2 E Red

Or course, it all depends on what you want. What are your requirements?
Not the specifics of what is in each table, but something like this:
"Select all products that have a dimension of 1 or 2 and have a color
assigned." That is the above query's criteria.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBQkdbf4echKqOuFEgEQJN+gCeOqaJSUrsib/Vyt9xLyshNlWQAh8AoLaG
ra6Do0ey6yMhskjYlGKwuYez
=9H1u
-----END PGP SIGNATURE-----

 

[Don]

MGFoster,

Yes, ID 9 would be included. My bad. Actually, I screwed up the desired
result! It should have been:

ID Shape Dimensions Product Color
1 Line 2 B
Green
2 Circle 2 C
Blue
5 Square 2
7 Triangle 2
9 Dot 1

Note that for ID 5, the square, while it meets the criteria of being 1 or 2
dimensional, the fact that it joins with Product E which is RED, it gets
excluded when using the query in the form:

SELECT S.ID, S.Shape, S.Dimension, P.ID AS LeftID, P.Color, P.Shape
FROM tblShape AS S LEFT JOIN tblProduct AS P ON S.ID = P.ID
WHERE S.Dimension IN (1,2) AND ((P.Color IS Null) OR (P.Color IN ("Green",
"Blue"))





Basically the requirements would be something like this:

Include _all_ records from tblShapes where Dimensions are in (1,2) and for
those records in tblShapes whose color is Green or Blue, include that
information.

In effect it would be doing the same as creating a table using the following
query:

SELECT P.ID, P.COLOR, P.SHAPE
FROM tblProduct AS P
WHERE P.Color IN ("Green", "Blue")

then doing the following on the resulting table

SELECT S.ID, S.Shape, S.Dimension, R.ID AS LeftID, R.Color, R.Shape
FROM tblShape AS S LEFT JOIN tblFirstQueryResult AS R ON S.ID = R.ID
WHERE S.Dimension IN (1,2)

What I do not have a grasp of is if (how) the results of a subquery can be
used in a JOIN.

Thanks!

Don

 

[MGFoster]

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

Using your 2 example queries and an undocumented feature of JET SQL:

SELECT S.ID, S.Shape, S.Dimension, R.ID AS LeftID, R.Color, R.Shape
FROM tblShape AS S
LEFT JOIN [SELECT P.ID, P.COLOR, P.SHAPE
FROM tblProduct AS P
WHERE P.Color IN ("Green", "Blue")]. AS R ON S.ID = R.ID
WHERE S.Dimension IN (1,2)

If you're using the Access 2002/3 ANSI SQL-92 option replace the square
brackets, including the concluding period, []. with parentheses.

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

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

iQA/AwUBQkhaWIechKqOuFEgEQIXFgCgyv9dW/86RDT556CWJV4vB9XIPRkAoPyb
M/atszcb7fxrYQv0CrTkmn9V
=j1Mw
-----END PGP SIGNATURE-----

 

[Don]

MGFoster,

I will be away from this project for a couple of days, but will try your
example as soon as I get back to it.

Thanks!

Don

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

Using your 2 example queries and an undocumented feature of JET SQL:

SELECT S.ID, S.Shape, S.Dimension, R.ID AS LeftID, R.Color, R.Shape
FROM tblShape AS S
LEFT JOIN [SELECT P.ID, P.COLOR, P.SHAPE
FROM tblProduct AS P
WHERE P.Color IN ("Green", "Blue")]. AS R ON S.ID = R.ID
WHERE S.Dimension IN (1,2)

If you're using the Access 2002/3 ANSI SQL-92 option replace the square
brackets, including the concluding period, []. with parentheses.

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

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

iQA/AwUBQkhaWIechKqOuFEgEQIXFgCgyv9dW/86RDT556CWJV4vB9XIPRkAoPyb
M/atszcb7fxrYQv0CrTkmn9V
=j1Mw
-----END PGP SIGNATURE-----

MGFoster,

Yes, ID 9 would be included. My bad. Actually, I screwed up the desired
result! It should have been:

ID Shape Dimensions Product Color
1 Line 2 B
Green
2 Circle 2 C
Blue
5 Square 2
7 Triangle 2
9 Dot 1

Note that for ID 5, the square, while it meets the criteria of being 1 or 2
dimensional, the fact that it joins with Product E which is RED, it gets
excluded when using the query in the form:

SELECT S.ID, S.Shape, S.Dimension, P.ID AS LeftID, P.Color, P.Shape
FROM tblShape AS S LEFT JOIN tblProduct AS P ON S.ID = P.ID
WHERE S.Dimension IN (1,2) AND ((P.Color IS Null) OR (P.Color IN ("Green",
"Blue"))





Basically the requirements would be something like this:

Include _all_ records from tblShapes where Dimensions are in (1,2) and for
those records in tblShapes whose color is Green or Blue, include that
information.

In effect it would be doing the same as creating a table using the following
query:

SELECT P.ID, P.COLOR, P.SHAPE
FROM tblProduct AS P
WHERE P.Color IN ("Green", "Blue")

then doing the following on the resulting table

SELECT S.ID, S.Shape, S.Dimension, R.ID AS LeftID, R.Color, R.Shape
FROM tblShape AS S LEFT JOIN tblFirstQueryResult AS R ON S.ID = R.ID
WHERE S.Dimension IN (1,2)

What I do not have a grasp of is if (how) the results of a subquery can be
used in a JOIN.

Thanks!

Don
















where a


on my
does

not