SELECT
    Stock.StockID, Keys=
      Case VehicleInventory.InventoryCode
            when null then 'NO'
            else 'Yes'
      end
    ContactDetail.LastName as 'Adjuster',
    AssignorStock.ClaimNumber as 'Claim #',
    Vehicle.VIN,
    Vehicle.Year, Vehicle.Make, Vehicle.Model,
    convert(varchar(10), Sale.SaleDate, 101) as SaleDate
FROM
     Aims.dbo.Stock Stock INNER JOIN Aims.dbo.Vehicle Vehicle ON
        Stock.StockID = Vehicle.StockID
     INNER JOIN AIMS.dbo.StockSale StockSale ON
        Stock.StockID = StockSale.StockID
     INNER JOIN Aims.dbo.AssignorStock AssignorStock ON
        Stock.AssignmentID = AssignorStock.AssignmentID
     INNER JOIN AIMS.dbo.Sale Sale ON
        StockSale.SaleID = Sale.SaleID
     INNER JOIN Aims.dbo.Assignor Assignor ON
        AssignorStock.AssignorID = Assignor.AssignorID
     INNER JOIN AIMS.dbo.Agent Agent ON
        AssignorStock.AgentID = Agent.AgentID
     left outer JOIN Aims.dbo.VehicleInventory VehicleInventory ON
        Vehicle.VehicleID = VehicleInventory.VehicleID
     INNER JOIN AIMS.dbo.ContactDetail ContactDetail ON
        Agent.ContactID = ContactDetail.ContactID
     INNER JOIN Aims.dbo.Company Company ON
        Assignor.CompanyID = Company.CompanyID
Where
            VehicleInventory.InventoryCode='KEYS'
and
            convert(varchar(8), AssignorStock.AssignorID) like
@AssignorID
and
            convert(varchar(8), AssignorStock.AgentID) like @AdjusterID
and
            convert(varchar(8), company.CompanyID) like @CompanyID
ORDER BY
    Vehicle.StockID ASC

quote:

---

Originally posted by tduggan

DDL and DML are needed in order to help. DDL would be CREATE TABLE statements for all tables involved. DML would be INSERT INTO statements for sample data. We would also need to know what the expected result set should look like using the sample data.

Tara

---

quote:

---

Originally posted by X002548

It's never going to return any "NO"s with an INNER JOIN...

The statement will have to elvaluate everything to be basically "TRUE"

If you did a LEFT JOIN then perhaps you would get "No"

---

     left outer JOIN Aims.dbo.VehicleInventory VehicleInventory ON
        Vehicle.VehicleID = VehicleInventory.VehicleID

  SELECT Stock.StockID
	, CASE WHEN vi.InventoryCode IS NULL THEN 'No'
            				     ELSE 'Yes'
	  END
      FROM Vehicle v
 LEFT JOIN VehicleInventory vi 
	ON v.VehicleID = vi.VehicleIDCompanyID
     WHERE vi.InventoryCode='KEYS'

quote:

---

Originally posted by X002548

What does

  SELECT Stock.StockID
	, CASE WHEN vi.InventoryCode IS NULL THEN 'No'
            				     ELSE 'Yes'
	  END
      FROM Vehicle v
 LEFT JOIN VehicleInventory vi 
	ON v.VehicleID = vi.VehicleIDCompanyID
     WHERE vi.InventoryCode='KEYS'

---