Multiple Left Join Issue

[SirDrums]

I am having a problem with using two left joins from a single table. This may be hard to explain but here it goes....

First. Below is a SS of my query in design view:

The reason I am using the same table twice with the same join is becasue as you can see in the fields I have selected from those table I have filtering for two specific relationship types in the relationship type field (3 and 88) the values in the ID1 field are primary keys for different tables and that is why both instances of the same table (relationship instance) are joined to two diffent tables at the end.

Ok on to my problem

The data that intially comes from the Piping table is a total of 4 records currently. But when I run it through the whole query it returns only two.

My understanding is that the left join is supposed to show all records from the Ppiping table but only the records from the other two tables that match....

Why does it seem like the other tables are filtering records out of the piping table? (There are supposed to 4 but it returns 2)

Thanks for any light you guys can shed on this issue.

 

[SirDrums]

I have a habit of over explaining things and then later figuring out how to boil it down some more...

I used the term left join when I think I should have used the term Outer join...so...

I have an outer join that by definition should include all the records from one table and only records from a second table that match.

but...

My join is acting like an inner join and only returning records where the both match...

confusing...

 

[SirDrums]

I am starting to understand a little bit, my creiteria in the piping table is turing the Outer Join into an inner join becasue the where part of the SQL statement executes after the joins...

Example:

SELECT PIPING.COMP_ID, RELATIONSHIPINSTANCE.RELATIONSHIPTYPE
FROM PIPING LEFT JOIN RELATIONSHIPINSTANCE ON PIPING.COMP_ID = RELATIONSHIPINSTANCE.IDS
WHERE PIPING.CLASS = AT_VALVE_GATE AND RELATIONSHIPINSTANCE.RELATIONSHIPTYPE = 88 OR IS NULL

So I guess I can see the problem now, I just don't know how to get around it....

 

[Pat Hartman]

Your relationships are not correctly defined. You are joining data field to data field. That's not how relational databases work. Just because the query engine lets you do it doesn't mean that your results will make any sense. A foreign key in the many-side table joins to the PRIMARY KEY of the one-side table. When you have multi-field primary keys, the foreign keys must also be multi-field so you can create a valid join. Because they are inconvenient for joins and not possible to use as combos, I just don't use multi-field primary keys except in rare instances. If I need a constraint on two columns, I create a unique index on those columns. So in your case, I would add an autonumber PK for relationshipinstance and then create a unique index on proj_ID and relationshipID.

If RelationshipID is an autonumber, it should be the PK and Proj_ID should probably be a foreign key to a table that isn't shown.

 

[SirDrums]

Thanks for the reply.

THis is not a relational DB in the traditional sence. The relationship are handled by the application that utilizes it.

I am just writing queries in it that shows the data in the ways we desire. Fortunaley I have been using the software for so long that I know how the table relate to each other. Beleive it or not, The way I have it joined is the way the app relates the tables.

I think I may and try creating this in two separate queries and the referencing them in my final....

 

[Pat Hartman]

The relationship are handled by the application that utilizes it.

 

[SirDrums]

yup. i just use it. I didnt program it. It works though.