在sql中加入四个表

本文介绍了在sql中加入四个表的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

亲爱的团队 我有4张桌子，我想加入所有四张桌子。 对于特定身份证件 在预订表中我们有一行数据 在分段表中我们有一行数据 在乘客表中我们有3行数据 在票价表中我们有3行数据 i使用以下方法

Dear Team I Have 4 tables, i want to join all the four tables. for particular id in Booking table we have one row of data in segment table we have one Row of data in passenger table we have 3 rows of data in fare table we have 3 rows of data i used the following method

SELECT Booking.Booking_ID, Booking.BookingDate, Booking.DomesticFlag, Booking.BookingAmt, Booking.CustomerCode, Segments.GPNR, Segments.PNR, Segments.FlightNo, Segments.Origin, Segments.Destination, Segments.Departure_Date, Segments.Arrival_Date, Segments.AirlinesVendor, Segments.FareID, Passengers.PassengerFName, Passengers.PassengerLName, Fares.Ticket_Number, Fares.Base_Fare, Fares.Airline_Tax, Fares.Service_Tax, Fares.Comission, Fares.AdminMarkUp, Fares.TDS, Fares.Fare_Id, Fares.Farebasis_Code, Fares.SupplierCommission, Fares.SupplierTDS, Fares.SupplierCode, Fares.DistributorCommission, Fares.DistributorTDS, Fares.AgentMarkup, Fares.EduCess, Fares.Cess, Fares.TaxValue, Fares.TaxName FROM Booking INNER JOIN Segments ON Booking.Booking_ID = Segments.Booking_ID INNER JOIN Passengers ON Segments.Booking_ID = Passengers.Booking_ID INNER JOIN Fares ON Passengers.Booking_ID = Fares.Booking_ID WHERE ( Booking.ClientID = '044' AND Booking.Booking_ID = 'NBAB3' AND Booking.Booking_ID = Segments.Booking_ID AND Passengers.Booking_ID = Fares.Booking_ID )

As根据以上Select语句，我得到9行响应， 可以任何人帮我解决这个问题 提前致谢 你的 Sheethal

As per the above Select statement I am getting 9 Rows Response, Can Any one help me to resolve this issue Thanks in Advance yours Sheethal

推荐答案

总是更好的使你的代码有点可读。 另外，你不需要那两个WHERE子句。我删除了它们。 试试这个： It's always better to make your code a bit READABLE. Plus, you don't need those two WHERE clause. I removed them. Try this one: SELECT Booking.Booking_ID, Booking.BookingDate, Booking.DomesticFlag, Booking.BookingAmt, Booking.CustomerCode, Segments.GPNR, Segments.PNR, Segments.FlightNo, Segments.Origin, Segments.Destination, Segments.Departure_Date, Segments.Arrival_Date, Segments.AirlinesVendor, Segments.FareID, Passengers.PassengerFName, Passengers.PassengerLName, Fares.Ticket_Number, Fares.Base_Fare, Fares.Airline_Tax, Fares.Service_Tax, Fares.Commission, Fares.AdminMarkUp, Fares.TDS, Fares.Fare_Id, Fares.Farebasis_Code, Fares.SupplierCommission, Fares.SupplierTDS, Fares.SupplierCode, Fares.DistributorCommission, Fares.DistributorTDS, Fares.AgentMarkup, Fares.EduCess, Fares.Cess, Fares.TaxValue, Fares.TaxName FROM Booking INNER JOIN Segments ON Booking.Booking_ID = Segments.Booking_ID INNER JOIN Passengers ON Passengers.Booking_ID = Segments.Booking_ID INNER JOIN Fares ON Fares.Booking_ID = Passengers.Booking_ID WHERE Booking.ClientID = '044' AND Booking.Booking_ID = 'NBAB3'

您期望什么输出？ 首先，让我解释一下这是如何工作的。由于您的INNER JOINS，您将获得9行，这些都是使用Booking_ID字段加入的。 那么这是怎么回事？我打算给你一个可能的场景。在预订表格中，您有Booking_ID ='NBAB3'。添加之后： Hi, What output do you expect? First of all, let me explain how this works. You're getting 9 rows because of your INNER JOINS, which are all joined using the Booking_ID field. So how this happens? I am going to give you a possible scenario. In the table "Booking" you have Booking_ID = 'NBAB3'. After adding this: INNER JOIN Segments ON Booking.Booking_ID = Segments.Booking_ID

您还有1行（1条记录） ，其中Segments.Booking_ID ='NBAB3'）。但在添加之后：

you still have 1 row (1 record, where Segments.Booking_ID = 'NBAB3'). But after adding this:

INNER JOIN Passengers ON Segments.Booking_ID = Passengers.Booking_ID

你有3行，因为在表乘客你有3条记录，其中Booking_ID ='NBAB3'。最后，添加

you have 3 rows because in the table "Passengers" you have 3 records, where Booking_ID = 'NBAB3'. Finally, after adding

INNER JOIN Fares ON Passengers.Booking_ID = Fares.Booking_ID

表中的乘客 3条记录，其中Booking_ID ='NBAB3'，在表票价中，您有3条记录，其中Booking_ID ='NBAB3'。所以你有9行（3 * 3），因为每个 Passengers.Booking_ID（3）匹配所有Fares.Booking_ID（3）。 我可以通过以下示例来说明这一点：

in the table "Passengers" you have 3 records, where Booking_ID = 'NBAB3' and in the table "Fares" you have 3 records, where Booking_ID = 'NBAB3'. So you have 9 rows (3 * 3) because each of the Passengers.Booking_ID (3) matches all Fares.Booking_ID (3). I can illustrate this by the following sample:

CREATE TABLE t1 (t1_ID INT); CREATE TABLE t2 (ID INT, t1_ID INT); CREATE TABLE t3 (ID INT, t1_ID INT); CREATE TABLE t4 (ID INT, t1_ID INT); GO INSERT INTO t1 (t1_ID) VALUES (1); INSERT INTO t2 (ID, t1_ID) VALUES (11, 1); INSERT INTO t3 (ID, t1_ID) VALUES (111, 1), (112, 1), (113, 1); INSERT INTO t4 (ID, t1_ID) VALUES (1111, 1), (1112, 1), (1113, 1); GO SELECT * FROM t1; GO -- 1 row SELECT * FROM t1 INNER JOIN t2 ON t1.t1_ID = t2.t1_ID; GO -- 1 row SELECT * FROM t1 INNER JOIN t2 ON t1.t1_ID = t2.t1_ID INNER JOIN t3 ON t2.t1_ID = t3.t1_ID; GO -- 3 rows SELECT * FROM t1 INNER JOIN t2 ON t1.t1_ID = t2.t1_ID INNER JOIN t3 ON t2.t1_ID = t3.t1_ID INNER JOIN t4 ON t3.t1_ID = t4.t1_ID; GO -- 9 rows

顺便说一下， Varsha 和 Manas 是对的：你在WHERE语句中不需要这些行（stroked）：

By the way, Varsha and Manas are right: you don't need these lines in a WHERE statement (stroked):

AND Booking.Booking_ID = Segments.Booking_ID AND Passengers.Booking_ID = Fares.Booking_ID

底线：您需要修改您的JOINS或整个选择以满足您的要求。 我可以进一步帮助你，但我需要知道你期望的输出和一些更多细节...

The bottom line: you need to modify your JOINS or a whole select in order to meet your requirements. I could help you further, but I need to know what output do you expect and some more details...

亲爱的团队 感谢您的回复 没有解决我的要求，我的要求是如果乘客没有三个那么总行数不应该是三个 问候 SHEETHAL Dear Team Thanks for your response No it is not resolved my requirement, My requirement is like if no of passenger is three then total no of rows should be three Regards SHEETHAL