CODEWITHSUNDEEP
sqlleft-joinwhere-clause
Mantas89
Mantas89

Reputation: 3

How to use where clause in Query with LEFT JOIN?

how to write my sql query correct? I would like to use WHERE clause in query, but I don't know how is correct. This is my query with mistake:

**SELECT pil.[Buy-from Vendor No_],  pil.No_, pil.Amount, pil.Quantity 
FROM dbo.[„blk“ 2011$Purch_ Inv_ Line]AS pil
WHERE pil.Type=5
LEFT JOIN dbo.[„blk“ 2011$Purch_ Inv_ Header] AS pih
ON pil.[Document No_]=pih.No_
ORDER BY pil.amount**

Upvotes: 0

Views: 7790

Answers (6)

Raniendu Singh
Raniendu Singh

Reputation: 57

Where comes after the From clause. The correct query is 

   SELECT pil.[Buy-from Vendor No_],  pil.No_, pil.Amount, pil.Quantity 
    FROM dbo.[„blk“ 2011$Purch_ Inv_ Line]AS pil
    LEFT JOIN dbo.[„blk“ 2011$Purch_ Inv_ Header] AS pih
    ON pil.[Document No_]=pih.No_
    WHERE pil.Type=5
    ORDER BY pil.amount;

Upvotes: 1

Thangamani Palanisamy
Thangamani Palanisamy

Reputation: 5290

This is way you have handle left joins

SELECT 
    pil.[Buy-from Vendor No_] 
  ,  pil.No_, pil.Amount
  , pil.Quantity 
FROM dbo.[UAB „Arvi cukrus“ 2011$Purch_ Inv_ Line] pil
LEFT JOIN dbo.[„blk“ 2011$Purch_ Inv_ Header] Pih ON pil.[Document No_]=pih.No_
WHERE pil.Type=5
ORDER BY pil.amount

Upvotes: 1

paulslater19
paulslater19

Reputation: 5917

Put the where after all the joins:

SELECT pil.[Buy-from Vendor No_],  pil.No_, pil.Amount, pil.Quantity 
FROM dbo.[„blk“ 2011$Purch_ Inv_ Line]AS pil
LEFT JOIN dbo.[„blk“ 2011$Purch_ Inv_ Header] AS pih
ON pil.[Document No_]=pih.No_
WHERE pil.Type=5
ORDER BY pil.amoun

Upvotes: 0

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79929

Move the WHERE clause after the FROM clause and other table references like so:

SELECT 
  pil.[Buy-from Vendor No_], 
  pil.No_, 
  pil.Amount, 
  pil.Quantity 
FROM ...
...
WHERE pil.Type=5
ORDER BY pil.amount

Upvotes: 0

Knaģis
Knaģis

Reputation: 21485

Move the WHERE clause right before ORDER BY.

Here is the documentation that defines how SELECT statements should look like: http://msdn.microsoft.com/en-us/library/ms189499.aspx.

Upvotes: 1

Grzegorz Grzybek
Grzegorz Grzybek

Reputation: 6237

put WHERE just before ORDER BY

Upvotes: 0

Related Questions