SQL Server中的连接查询问题

Below is the query which works but with some errors:

下面的查询是有效的，但有一些错误:

SELECT dbo.Booking.Cost, 
       dbo.Booking.StatusID, 
       dbo.Account.FirstName, 
       dbo.Attendee.HelmetsPurchased AS ProductsPurchased, 
       dbo.Attendee.GaragesPurchased, 
       dbo.Attendee.SecondDriver AS [Driver Name]
  FROM dbo.Booking 
  JOIN dbo.Attendee ON dbo.Booking.EventID = dbo.Attendee.EventID 
  JOIN dbo.Account ON dbo.Booking.UserID = dbo.Account.UserID
 WHERE (dbo.Booking.EventID = 15)

Output:

输出:

The issue with the generated table is the column product purchased and driver name is populated with text for every row which in this case it should be populated for only the rows which has Drivername and product purchased. There is only one row in the attendee table which has drivername, product purchase fields populated for username mark, while all the other rows has null value for both the drivername and products.

生成的表的问题是购买的列产品，驱动程序名填充了每一行的文本，在这种情况下，应该只填充有驱动程序名和产品购买的行。在attendee表中只有一行包含驱动名、为用户名填充的产品购买字段，而其他所有行都包含驱动名和产品的空值。

2 个解决方案

#1

If you don't need driver name, the following could help:

如果你不需要司机的名字，以下可以帮到你:

SELECT
    B.EventId,
    B.Cost,
    B.StatusID,
    A.FirstName,
    SUM(AT.HelmetsPurchased) AS ProductsPurchased,
    SUM(AT.GaragesPurchased) AS GaragesPurchased
FROM
    dbo.Booking B
INNER JOIN
    dbo.Account A ON B.UserID = A.UserID
LEFT OUTER JOIN
    dbo.Attendee AT ON B.EventID = AT.EventID 
WHERE (dbo.Booking.EventID = 15)
GROUP BY B.EventId, B.Cost, B.StatusId, A.FirstName

#2

Instead of an INNER JOIN on the table Attendee you should do a LEFT JOIN. This will include rows where these two columns are null as well.

与表上的内部连接不同，您应该执行左连接。这将包括这两列为空的行。

   SELECT b.Cost, 
          b.StatusID, 
          ac.FirstName, 
          at.HelmetsPurchased AS ProductsPurchased, 
          at.GaragesPurchased, 
          at.SecondDriver AS [Driver Name]
     FROM dbo.Booking b
LEFT JOIN dbo.Attendee at ON b.EventID = at.EventID 
LEFT JOIN dbo.Account ac ON b.UserID = ac.UserID
    WHERE b.EventID = 15