Stephen Stephen - 1 year ago 67
SQL Question

Too many results in query

I'm fetching some data from our database in MSSQL. Out of this data I want to determine who created the client entry and who took the first payment from this client.

There can be many payment entries for a client on a single booking/enquiry and at the moment, my query shows results for each payment. How can I limit the output to only show the first payment entry?

My query:

DATEDIFF(day, c.DateOfCreation, p.DateOfCreation) AS DaysToPayment,
CONCAT(u.FirstName, ' ', u.LastName) AS CreateUser,
(SELECT CONCAT(u.FirstName, ' ', u.LastName)
WHERE u.UserID = p.UserID ) AS PaymentUser

FROM tblBookings b

INNER JOIN tblPayments p
ON b.BookingID = p.BookingID
INNER JOIN tblEnquiries e
ON e.EnquiryID = b.EnquiryID
INNER JOIN tblCustomers c
ON c.CustomerID = e.CustomerID
INNER JOIN tblOrigins o
ON o.OriginID = e.OriginID
INNER JOIN tblSuperOrigins s
ON s.SuperOriginID = o.SuperOriginID
INNER JOIN tblBookingPackages bp
ON bp.bookingID = p.BookingID
INNER JOIN tblPackages pc
ON pc.PackageID = bp.packageID
INNER JOIN tblUsers u
ON u.UserID = c.UserID

WHERE c.DateOfCreation >= '2016-06-01' AND c.DateOfCreation < '2016-06-30'
AND p.PaymentStatusID IN (1,2)
AND e.CustomerID = c.CustomerID
AND p.DeleteMark != 1
AND c.DeleteMark != 1
AND b.DeleteMark != 1

I tried adding a "TOP 1" to the nested select statement for PaymentUser, but it made no difference.

Answer Source

you can use cross apply with top 1:

FROM tblBookings b cross apply (select top 1 * from tblPayments p where b.BookingID = p.BookingID) as p

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download