Outer join problem in SQL Server

Question

This is my query. And I want all rows of BF and matched rows of FT. It returns only 2 rows but the real row count is 135. How should I change my query?

SELECT 
  BF.FaturaNo,
  K.KlinikAdi,
  FT.teslimAlindi,
  FT.TeslimAciklama,
  FT.kurumTeslimAldi,
  FT.kurumTeslimAciklama,
  BF.basilmisFatura_id,
  K.klinik_id
FROM 
  BasilmisFaturalar AS BF
LEFT OUTER JOIN 
  FaturaTeslim AS FT 
  ON FT.refBasilmisFatura_id = BF.BasilmisFatura_id
INNER JOIN 
  Klinikler AS K 
  ON BF.refKlinik_id = K.klinik_id
INNER JOIN 
  Faturalar AS F 
  ON F.refBasilmisFatura_id = BF.basilmisFatura_id
WHERE 
      BF.FaturaNo LIKE '%%'
  AND BF.refklinik_id IN ('24','25','26','27')
  AND MONTH(F.faturaTarihi) = 7 
  AND teslimAlindi = 0 
  AND kurumTeslimAldi = 0
GROUP BY 
  F.refBasilmisFatura_id,
  BF.FaturaNo,
  K.KlinikAdi,
  FT.teslimAlindi,
  FT.TeslimAciklama,
  FT.kurumTeslimAldi,
  FT.kurumTeslimAciklama,
  BF.basilmisFatura_id,
  K.klinik_id
ORDER BY
  faturaNo ASC

Answer 1

Your WHERE clause is probably more restrictive than you think it is. Try taking out some of those terms.

Also, BF.FaturaNo LIKE '%%' should probably be replaced with more comprehensible BF.FaturaNo IS NOT NULL

Try reducing your problem to a simpler demonstration of the concept. You've got too much data-model-specific stuff built into this question.

Answer 2

Your teslimAlindi and kurumTeslimAldi filter are on the "OUTER" side so make this BF join an INNER JOIN.

You need to filter first before joining or filter in the ON clause. I prefer this the derived table approach

    ....
FROM BasilmisFaturalar AS BF
    LEFT OUTER JOIN
    (SELECT *
    FROM
        FaturaTeslim
    WHERE
        teslimAlindi = 0 AND 
        kurumTeslimAldi = 0
    )  AS FT ON FT.refBasilmisFatura_id = BF.BasilmisFatura_id
    INNER JOIN Klinikler AS K ON BF.refKlinik_id = K.klinik_id
    INNER JOIN Faturalar AS F ON F.refBasilmisFatura_id = BF.basilmisFatura_id
WHERE BF.FaturaNo LIKE '%%' AND 
    BF.refklinik_id IN ('24','25','26','27') AND 
    MONTH(F.faturaTarihi) = 7

Answer 3

You have teslimAllindi and kurumTeslimAldi in your where clause.

The join is execute before the where clause. So for every row where there is no FakturaTeslim record, the TeslimAllindi and KurumTeslimAldi will be NULL after the join.

Those rows will be filtered out by the where clause, effectively making the left join work as an inner join.

You should write this instead:

SELECT 
  BF.FaturaNo,
  K.KlinikAdi,
  FT.teslimAlindi,
  FT.TeslimAciklama,
  FT.kurumTeslimAldi,
  FT.kurumTeslimAciklama,
  BF.basilmisFatura_id,
  K.klinik_id
FROM 
  BasilmisFaturalar AS BF
LEFT OUTER JOIN 
  FaturaTeslim AS FT 
  ON 
    (FT.refBasilmisFatura_id = BF.BasilmisFatura_id
     AND teslimAlindi = 0 
     AND kurumTeslimAldi = 0)
INNER JOIN 
  Klinikler AS K 
  ON BF.refKlinik_id = K.klinik_id
INNER JOIN 
  Faturalar AS F 
  ON F.refBasilmisFatura_id = BF.basilmisFatura_id
WHERE 
      BF.FaturaNo LIKE '%%'
  AND BF.refklinik_id IN ('24','25','26','27')
  AND MONTH(F.faturaTarihi) = 7 
GROUP BY 
  F.refBasilmisFatura_id,
  BF.FaturaNo,
  K.KlinikAdi,
  FT.teslimAlindi,
  FT.TeslimAciklama,
  FT.kurumTeslimAldi,
  FT.kurumTeslimAciklama,
  BF.basilmisFatura_id,
  K.klinik_id
ORDER BY
  faturaNo ASC

Answer 4

The WHERE clause has

 AND teslimAlindi = 0  
 AND kurumTeslimAldi = 0

Assuming those unaliased columns are from table FaturaTeslim, what you are saying is effectively:
- Get all desired rows from BasilmisFaturalar
- Match them with any rows found in FaturaTeslim, but it's ok if none are found
- And of these results, toss out all rows where teslimAlindi <> 0 or kurumTeslimAldi <> 0

WIth the left outer join, for those rows where no matches were found in FaturaTeslim, teslimAlindi and kurumTeslimAldi will be NULL, and that gets them removed by the where clause (because they're not = 0).

gbn's code snippet shows how to fix this, by checking for those values in the outer join's ON clause -- which works IF that is the business logic you wish to implement (that is, only LOJ those FaturaTeslim rows where teslimAlindi = 0 and kurumTeslimAldi = 0