Problem with join query in 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.SecondDri开发者_StackOverflowver 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.

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

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