Joining four Tables in sql

Question

0.00/5 (No votes)

See more:

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

SQL

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

Posted 15-Apr-14 4:49am

smsheethal

Updated 15-Apr-14 10:41am

Andrius Leonavicius

v3

Add a Solution

Comments

Varsha Ramnani 15-Apr-14 11:47am

why are you using linking condition of booking_id in join as well as in where ... you don't need to do that condition check in where clause

5 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Manas Bhardwaj · Answer 1 · 2014-04-15T08:31:00

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:

SQL

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'

Andrius Leonavicius · Answer 2 · 2014-04-15T10:39:00

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:

SQL

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:

SQL

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

SQL

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:

SQL

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

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

SQL

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

smsheethal · Answer 3 · 2014-04-15T19:29:00

Solution 3

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

Posted 15-Apr-14 19:29pm

smsheethal

Sanket Saxena · Answer 4 · 2014-04-15T19:42:00

Solution 4

Seems like your problem is in INNER JOIN. If you want No. of passengers the you can use LEFT JOIN for the rest of the table with passengers table.

Hope it helps :)

Posted 15-Apr-14 19:42pm

Sanket Saxena

Updated 15-Apr-14 19:43pm

v2

N Haque · Answer 5 · 2014-04-15T21:00:00

Try this way

SQL

SELECT
    Booking.Booking_ID,
    .,
    .,
    .
FROM
    Passengers LEFT JOIN  Booking ON 
        Passengers.Booking_ID= Booking.Booking_ID 
    INNER JOIN Segments ON
        Booking.Booking_ID = Segments.Booking_ID    
    INNER JOIN Fares ON
        Fares.Booking_ID = Passengers.Booking_ID
WHERE
    Booking.ClientID = '044'
    AND Booking.Booking_ID = 'NBAB3'

Joining four Tables in sql

5 solutions

Solution 1

Solution 2

Solution 3

Solution 4

Solution 5

Add your solution here

Preview 0