Clever Geek Handbook

Conditional sentence Where is the alternative

I am using xml to display the result of a query. I have one problem related to my request. Let's say in a customer table, I have data whose ID is not a forigne in any of the other tables, but still I want to display the data. i a using left join for this. But the problem is related to other table conditions (Where clause pr.fActive = 1 ...), due to which I cannot display data that is only in the customers table, but not in another table. How can I do that

<Table>customer cu</Table>
          <Joins>
            left join customerprogramxref cuprxref on cu.ixcustomer=cuprxref.ixcustomer
            left join tblprogram  pr on cuprxref.ixprogram=pr.ixprogram
            left join programworkpackagexref prwpxref on pr.ixprogram= prwpxref.ixprogram
            left join workpackage wp on wp.ixworkpackage =prwpxref.ixworkpackage
            left join workpackageactivityxref wpactxref on              wpactxref.ixworkpackage=wp.ixworkpackage
            left join activity act on act.ixactivity=wpactxref.ixactivity
          </Joins>
          <WhereClause>
            cu.fStatus=1 AND pr.fActive=1 AND pr.fDeleted=0 AND wp.fStatus=1 AND act.fStatus=1
          </WhereClause>
+3


source to share


4 answers


Because of the LEFT JOIN

right side, you have NULL when not connected.

Try the following:



cu.fStatus=1
        AND (pr.fActive=1 OR pr.fActive IS NULL)
        AND (pr.fDeleted=0 OR pr.fDeleted IS NULL)
        AND ...
+2


source


try setting other conditions like (Where where pr.fActive = 1 ...) with its relative left joins using AND

Example...



LEFT JOIN tblprogram pr ON cuprxref.ixprogram = pr.ixprogram AND pr.fActive = 1 AND pr.fDeleted = 0

Hope this helps.

+3


source


Check it out in the Where section:

 cu.fStatus=1 OR ( pr.fActive=1 AND pr.fDeleted=0 AND wp.fStatus=1 AND act.fStatus=1)
+2


source


Can't you try nested queries:

something like that,

SELECT finalMap.ixCustomer,finalMap.ixprogram, finalMap.ixWorkPackage,
FROM customer t
left outer join
(SELECT t.ixCustomer,cpRef.ixprogram, cpRef.ixWorkPackage
 FROM customerprogramxref t
left outer join
(SELECT pRef.ixprogram, pRef.ixWorkPackage FROM tblprogram pr
left outer join
(SELECT t.ixprogram,w.ixWorkPackage FROM programworkpackagexref t
left outer join
(SELECT wpa.ixWorkPackage FROM workpackage w
left outer join
(SELECT wpactxref.ixWorkPackage FROM workpackageactivityxref wpactxref
left outer join (SELECT t.ixactivity FROM activity t where t.fStatus=1) act
on act.ixactivity=wpactxref.ixactivity) wpa on w.ixworkpackage =wpa.ixworkpackage
where w.fStatus=1) w  on t.ixWorkPackage = w.ixWorkPackage) pRef
on pr.ixprogram = pRef.ixprogram
where pr.fActive=1  AND pr.fDeleted=0)cpRef
on t.ixprogram=cpRef.ixprogram)finalMap
on t.ixCustomer=finalMap.ixCustomer
where t.fStatus=1;
+2


source






More articles:


All Articles