Mysql queries - using join/union etc

Question

I have 4 tables in my database named customers (holding their personal details and which room they were in what project), payments (holding their payment details for the said project), projects (holding project details) and staff (holding staff login details)

I also have 3 pages where I call for data from all these pages (except for staff - once they log in, sessions and etc. deal with those) but for customers, payments and projects I constantly need data.

The structure of tables are as follows;

Customers

---

UID - pID - rID - name - surname - roomtype

Payments

---

UID - pID - total - paid 

Projects

---

pID - name - hotel

As for results I want to check who (UID) were staying in the same room (rID) in the same project (pID) and also I want to check their payment details individually whether they were in a single room or double.

What queries could I use that would give me the result as below without causing too much stress on the server while having the least amount of queries as possible?

Example;

+-------------------------------------------------------------------+
| rID    UID    pID    name    surname    roomtype    total    paid |
+-------------------------------------------------------------------+
| 1035   1010   301    John    Doe        double      200      200  |
|        1011   301    Jane    Doe        double      200      200  |
| 1036   1012   301    Fred    Nerk       single      150      150  |
+-------------------------------------------------------------------+

EDIT:

I've changed my query to;

$query = "SELECT p1.*,
    p2.total, p2.paid, p2.pmethod, p2.currency, p2.auth, p2.invoice,
    p3.pname, p3.pstartdate, p3.penddate, p3.photel, p3.pcity, p3.pstatus
    FROM customers p1 
    INNER JOIN payments p2 ON p1.UID=p2.UID
    INNER JOIN projects p3 ON p1.pID=p3.pID";
$guests = $mysqli->prepare($query);
$guests->execute();
$guests->store_result();

Although when it comes to $guests->fetch() I do not get any results. I've checked the query through MYSQL Workbench as @Code-Monk has suggested - while it selects the rows, it doesn't select any of the data stored inside the tables and thus returns no results. Can anyone point out my mistake here?

• CONCLUSION - Apparently I had the "UID" entries in the customers table different than the rest. So, now it works. Thank you very much for helping me out with this, honestly you've saved me much work and much time. Know that you are amazing and I appreciate the time you've spared to help me. Thank you again! Kudos and good karma to you :)

Answer 1

Try Out this:

select p1.*,p2.total,p2.paid,p3.hotel from customers p1 
inner join payments p2 on p1.uid=p2.uid 
inner join projects p3 on p1.pid=p3.pid