SQL to find the orders that vendors has sold

Sup guys so I can´t get any result from this SQL:


SELECT[Order].CustomOrderNumber, [Order].OrderTotal, [Order].OrderDiscount, [DiscountUsageHistory].DiscountID, [DiscountUsageHistory].OrderID, [Discount].ID, [Discount].Name, [Discount].VendorCommissions, [Discount].Usagedate, [Vendor].Id

FROM[Order]

INNER JOIN [DiscountUsageHistory] ON [Order].CustomOrderNumber = [DiscountUsageHistory].OrderID
INNER JOIN [Discount] ON [DiscountUsageHistory].DiscountID = [Discount].ID
INNER JOIN [Vendor] ON [Discount].VendorID = [Vendor].Id AND [Discount].Id = [Vendor].DiscountID
        
WHERE[Order].OrderDiscount > 0 AND [Vendor].Id = 1 (id vendor logged in)


is something wrong ? Thanks !

RE: ...ON [Order].CustomOrderNumber = [DiscountUsageHistory].OrderID

You should join [Order].OrderId = [DiscountUsageHistory].OrderID
(and you should LEFT JOIN, because a discount may not have been used)

Also, did you add custom fields to your tables?
Discount does not have VendorId and Vendor does not have DiscountId

INNER JOIN [Vendor] ON [Discount].VendorID = [Vendor].Id AND [Discount].Id = [Vendor].DiscountID

Hi yes I added custom fields to my tables

the field OrderID on the table Order is the CustomNumberOrder

Solution:



SqlCommand cmd = new SqlCommand(
              "SELECT[Order].CustomOrderNumber, [Order].OrderTotal, [Order].OrderDiscount, [DiscountUsageHistory].DiscountId, [DiscountUsageHistory].OrderId, [Discount].Id, [Discount].Name, [Discount].VendorCommissions, [Discount].Usagedate " +
              " FROM[Order] " +
              " INNER JOIN[DiscountUsageHistory] ON[Order].Id = [DiscountUsageHistory].OrderId " +
              " INNER JOIN[Discount] ON[DiscountUsageHistory].DiscountId = [Discount].Id " +
              " INNER JOIN[Vendor] ON[Discount].VendorID = [Vendor].Id " +
              " WHERE [Vendor].Id = " + currentuser