Ayman Ayman - 5 months ago 6
SQL Question

Query returns no records

Hello i am making schema for purchasing orders these orders can be ordered by certain user and then received by another user.

so i created the below schema.

Schema

Schema

The issue is when

UserID
column in
PurchaseOrders
and
Deliveries
tables has different values the query returns no records.

Query

SELECT
dbo.Users.FirstName,
dbo.Users.LastName,
dbo.PurchaseOrders.PurchaseOrderDate,
dbo.Deliveries.ExpectedDeliveryDate,
dbo.Deliveries.ActualDeliveryDate
FROM dbo.PurchaseOrders
INNER JOIN dbo.Users
ON dbo.PurchaseOrders.UserID = dbo.Users.UserID
INNER JOIN dbo.Deliveries
ON dbo.PurchaseOrders.PurchaseOrderID = dbo.Deliveries.PurchaseOrderID
AND dbo.Users.UserID = dbo.PurchaseOrders.UserID
AND dbo.Users.UserID = dbo.Deliveries.UserID

Answer

You need two different joins to Users. You also need to learn to use table aliases:

SELECT pu.FirstName as purchase_FirstName, pu.LastName as purchase_LastName,
       du.FirstName as delivery_FirstName, du.LastName as delivery_LastName,
       po.PurchaseOrderDate,
       d.ExpectedDeliveryDate, d.ActualDeliveryDate
FROM dbo.PurchaseOrders po JOIN
     dbo.Deliveries d
     ON po.PurchaseOrderID = d.PurchaseOrderID JOIN
     dbo.Users pu
     ON p.UserID = pu.UserID JOIN
     dbo.Users du
     ON d.UserId = du.UserId;

Your query returns no records because of this condition:

  AND dbo.Users.UserID = dbo.PurchaseOrders.UserID
  AND dbo.Users.UserID = dbo.Deliveries.UserID

This obviously means that dbo.PurchaseOrders.UserID = dbo.Deliveries.UserID. So, if this is not true, then no records match the condition.