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
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
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
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
SELECT * FROM t1
INNER JOIN t2
ON t1.t1_ID = t2.t1_ID;
GO
SELECT * FROM t1
INNER JOIN t2
ON t1.t1_ID = t2.t1_ID
INNER JOIN t3
ON t2.t1_ID = t3.t1_ID;
GO
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
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
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...