Sql query question- how to get flight number

Question

0.00/5 (No votes)

See more:

FlightNumber PNR Origin Destination
216 AA0011 HOG NAS
229 AA0011 NAS MIA

216 AA0401 HOG NAS
229 AA0401 NAS MIA

222 AA0411 MIA NAS
215 AA0411 NAS HOG

222 AA0412 MIA NAS
215 AA0412 NAS HOG
I want output like this.
FlightNumber PNR Origin-Destination
229,216 AA0011 MIA-HOG
222,215 AA0411 MIA-HOG

What I have tried:

SELECT R.FlightNumber,PR.PNR,AO.IATACode AS Origin,AD.IATACode AS Destination FROM Reservations R 
									INNER JOIN PaxReservation PR ON R.PKID_Reservations=FKID_Reservation									
									INNER JOIN Airports AO on AO.PKID_Airports=r.FKID_Origin
                                    INNER JOIN Airports AD on AD.PKID_Airports=r.FKID_Destination 
									WHERE  OperatingStatus > 0 AND R.StartDateTime >= '06/11/2020' AND R.StartDateTime < '06/12/2020'

Posted 11-Jun-20 12:04pm

Member 10506503

Updated 11-Jun-20 13:52pm

Add a Solution

1 solution

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)

MadMyche · Accepted Answer · 2020-06-11T13:52:00

Well, here is some time spent on working this for you based on the data you did provide... unfortunately there are other tables that you did not give us the schema or data for

SQL

-- testing table and data population
declare @Temp table (
	FlightNumber int,
	PNR char(6),
	Origin char(3),
	Destination Char(3)
)
INSERT @Temp VALUES
  (216, 'AA0011', 'HOG', 'NAS')
, (229, 'AA0011', 'NAS', 'MIA')

, (216, 'AA0401', 'HOG', 'NAS')
, (229, 'AA0401', 'NAS', 'MIA')

, (222, 'AA0411', 'MIA', 'NAS')
, (215, 'AA0411', 'NAS', 'HOG')

, (222, 'AA0412', 'MIA', 'NAS')
, (215, 'AA0412', 'NAS', 'HOG')

/* [=====[ Proof of Concept ]=====]*/
SELECT t2.FlightNumber, t1.FlightNumber, t1.PNR, t2.Destination, t1.Origin
FROM   @Temp t1
JOIN   @Temp t2 ON t1.pnr = t2.PNR AND t1.Destination = t2.Origin
WHERE  T1.PNR LIKE '____11'