Dealing with orphaned/absent rows in join

[FrogmanL]

I have a query with that joins two tables. One table contains a part and part_id, and another table contains property_name, property_value, and related part_id.

The problem that I'm having is that every part may not have every property. If I join the part_id field, and select property_value where property_name="Size", then all of the Part_ids where the property_name="Size" does not exist are ignored.

If I just do property_name="Size" or property_name<>"Size" then I get all the records for any property. I just want to determine if the size property exists for this field, and if it doesn't I want a 0 there instead, without adding a bunch of rows back to my table.

I want the Size column in my query to display the size when that property is present, but display 0 or some other value when that property is not present.

How do I deal with these orphaned/absent rows in my table?

 

[r937]

LEFT OUTER JOIN, probably

it's kinda hard to say when we can't see your tables or your queries



r937.com | rudy.ca

 

[FrogmanL]

Here are my tables:

Table1
Part_ID Part
1 Thing1
2 Thing2
3 Thing3
4 Thing4
5 Thing5

Table2
Part_ID Property_Name Property_Value
1 Size 5.5
1 Weight 101
2 Size 10.2
3 Size 11.1
3 Weight 75
4 Weight 90
5 Size 4.7
5 Weight 85

Query1:
SELECT Table1.Part, Table2.Property_Value
FROM Table1 LEFT JOIN Table2 ON Table1.Part_ID = Table2.Part_ID
WHERE (((Table2.Property_Name)="Size"));

Query1 Results:
Part Property_Value
Thing1 5.5
Thing2 10.2
Thing3 11.1
Thing5 4.7

As you can see from the results, Thing4 is ignored because it has no corresponding row in Table2.

How do I handle this absent row to produce a value like Thing4 size=0 without simply adding that to Table2?

 

[PHV]

Testing for Table2.Property_Name in the WHERE clause defeats the purpose of the LEFT JOIN.
One way:
SELECT A.Part, B.Property_Value
FROM Table1 AS A LEFT JOIN (
SELECT Part_ID, Property_Value FROM Table2 WHERE Property_Name = 'Size'
) AS B ON A.Part_ID = B.Part_ID

Another way:
SELECT A.Part, B.Property_Value
FROM Table1 AS A LEFT JOIN Table2 AS B ON A.Part_ID = B.Part_ID
WHERE Nz(B.Property_Name,'Size')='Size'

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886