MySQL查询-使用联接/联合等 [英] Mysql queries - using join/union etc

问题描述

我的数据库中有4个表，分别名为 customers (保存他们的个人详细信息和他们在哪个项目中的房间)，付款(保存他们的付款详细信息)项目)，项目(保存项目详细信息)和工作人员(保存人员登录详细信息)

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)

我还有3个页面，我需要从所有这些页面中调用数据(员工除外-一旦他们登录，会话等处理这些信息)，但是对于客户，付款和项目，我一直需要数据.

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.

表的结构如下；

UID - pID - rID - name - surname - roomtype

付款

---

UID - pID - total - paid 

项目

---

pID - name - hotel

关于结果，我想检查谁(UID)住在同一项目(pID)中的同一房间(rID)，并且我想分别检查他们的付款详细信息，无论他们是单人房间还是双人间.

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?

示例；

+-------------------------------------------------------------------+
| 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  |
+-------------------------------------------------------------------+

我将查询更改为；

$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();

尽管在$ guests-> fetch()方面我没有得到任何结果.我已经按照@ Code-Monk的建议通过MYSQL Workbench检查了查询-选择行时，它不选择存储在表内的任何数据，因此不返回任何结果.有人可以在这里指出我的错误吗?

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?

• 结论- 显然，客户表中的"UID"条目与其余表不同.因此，现在可以使用了.非常感谢您为我提供帮助，说实话，您为我节省了很多工作和时间.知道您很棒，我感谢您抽出宝贵的时间来帮助我.再次感谢你！恭喜您，祝您一切顺利:)

• 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 :)

推荐答案

尝试一下:

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

这篇关于MySQL查询-使用联接/联合等的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！