LEFT JOIN query

[ShadowMage]

Hey guys, I have the following query:

SELECT Part.PartNum, PartTran.TranDate, PartTran.TranQty, PartTran.MtlUnitCost, Part.AvgMaterialCost FROM Part Part LEFT OUTER JOIN PartTran PartTran ON (Part.Company = PartTran.Company AND Part.PartNum = PartTran.PartNum) WHERE ((Part.InActive = 0) AND (PartTran.TranDate >= '2010-10-14') AND (PartTran.TranType LIKE 'PUR-%')) ORDER BY Part.PartNum ASC, PartTran.TranDate DESC

It's supposed to pull all active parts from the part table whether they have a matching record in the PartTran table or not. I thought that's what the LEFT OUTER JOIN would do, but I get the exact same results whether I use the LEFT OUTER JOIN or INNER JOIN. Is there something else I'm missing?Help would be much appreciated. Thanks.

[birbal]

in left outer join it will show you the all row from left table and matched tow from right side and unmatched row in right table will represned as NULL.Where as in inner join it will show only the matching rows.if it is returning the same rows in both case that means all rows are matching from both tables.so te results are looking same.

[birbal]

and one more thing removing the unnescary braces in WHERE clause will make faster your queries.

[ShadowMage]

if it is returning the same rows in both case that means all rows are matching from both tables.so te results are looking same.

But I can guarantee that is not the case, and in fact I have checked, double checked, and triple checked. There are numerous parts that do not have any transactions against them in the last 6 months. I know how LEFT JOINS and INNER JOINS work, which is why I am confused as to why the LEFT JOIN pulls the same records as the INNER JOIN. Oh, and thanks for the little tidbit about the parens. Didn't know that.

[birbal]

But I can guarantee that is not the case, and in fact I have checked, double checked, and triple checked. There are numerous parts that do not have any transactions against them in the last 6 months. I know how LEFT JOINS and INNER JOINS work, which is why I am confused as to why the LEFT JOIN pulls the same records as the INNER JOIN. wacko.gif

I am not sure. can you remove the WHERE clause for a while to be confirm what joined table coming? i belive its returning the joined tables properly ..if it has not matched row it is showing them as NULL. WHERE clause is checking the condition upon the joined table. and probably its filtering the results so that it seems same in both case.