Roger <roger-FNkchr/[EMAIL PROTECTED]> wrote:
I have the following query
CREATE VIEW MyStuff AS
SELECT Com . CompanyID CompID , PublicationID PubID ,
RelationshipDescription Relationship , CompanyName Name , BranchName
Branch , BranchID
FROM Publication_Branch_Relationships AS PBRel
LEFT JOIN ltblPublication_Branch_Relationship AS BRel ON PBRel .
RelationshipID = BRel . RelationshipID
LEFT JOIN Companies AS Com ON PBrel . CompanyID = Com . CompanyID
LEFT JOIN Branches AS Branches ON Branches . CompanyID = Com .
CompanyID
ORDER BY PublicationID , RelationshipDescription
Now when i issue out a query i.e
Select *
From MyStuff
Where CompID = 3562;
I dont get anything from that result, seems like a bug. But i can
query on all the other columns.
Do you have any records in the Companies table with CompanyID = 3562 ?
Note Companies is on the right side of left join with
Publication_Branch_Relationships. So if you have CompanyID = 3562 in
PBRel but not in Com, PBrel.CompanyID will be 3562 but Com.CompanyID
will be NULL. And your view looks at the latter, not the former.
By the same token, when you join in Branches table, do you really want
to join on Branches.CompanyID=Com.CompanyID, and not on
Branches.CompanyID=PBRel.CompanyID ? With the query as written, if
there's some CompanyID for which there's a record in Branches but not in
Companies, the Branches data will not be included in the resultset
(since Com.CompanyID will be NULL).
Igor Tandetnik
