MySQL 3 table overlapping query

Question

Hello, another 3 table query here. I have a table reservation, customer_service, and billing.

I am trying to select roomtype from reservation and some other fields from billing "ON" r.ID = b.rID and the same for customer_services (shown below).

The first part before the union works fine, but when adding the union I get a weird side effect that any result with a duplicate r.Roomtype, b.Quantity, b.UnitPrice, and b.Total get dropped.

How do I avoid that, since they will have different b.reservationID and I want the duplicates?

SELECT r.RoomType, b.Quantity, b.UnitPrice, b.Total FROM Billing b, Reservation r
WHERE b.ReservationID = r.ReservationID
      AND b.UserName = "Stuart"
      AND b.Paid = "0"

UNION

SELECT cs.ServiceName, b.Quantity, b.UnitPrice, b.Total FROM Customer_Service cs, Billing b
WHERE b.CustomerServiceID = cs.CustomerServiceID
      AND b.UserName = "Stuart"
      AND b.Paid = "0";

Answer 1

Make use of UNION ALL instead of just UNION

See UNION Syntax