SQL query to select records which is common for my first column

Question

0.00/5 (No votes)

See more:

SEG_AIRLINE	                SEG_ORIGIN_CODE  	SEG_DESTINATION_CODE
        G9				ALA                             DEL
        G9				ALA				DAC
        G9				BKK				OOL
        SG				IXA				MAA
        SG				BKK				OOL		
        SG				IXA				GAU
        6E				IXA				AMD
        6E				BKK				OOL
        6E				IXA				DEL
        AK				BKK				MEL
        AK				BKK				OOL
        AK				BKK				PER

I have a table as like above. Now i want to select SEG_ORIGIN_CODE AND SEG_DESTINATION_CODE
which is common to all SEG_AIRLINE.
I want output like

SEG_AIRLINE	SEG_ORIGIN_CODE	             SEG_DESTINATION_CODE
G9				BKK     		        OOL        
SG				BKK				OOL        
6E				BKK				OOL        
AK				BKK				OOL

Posted 3-Jan-14 18:19pm

Bond487

Updated 3-Jan-14 21:04pm

Christian Graus

v3

Add a Solution

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

Christian Graus · Answer 1 · 2014-01-03T18:27:00

The quick way to do this, is with subqueries.

SQL

select seg_airline, seg_origin_code, seg_destination_code from seg
where
(Select count(distinct seg_airline) from seg s where s.seg_origin_code = seg.seg_origin_code) = 4
and
(Select count(distinct seg_airline) from seg s where s.seg_destination_code = seg.seg_destination_code) = 4

To make it continue to work no matter how many airlines get added, you can do this;

SQL

declare @airlineCount int
select @airlineCount = count(distinct seg_airline) from seg

select seg_airline, seg_origin_code, seg_destination_code from seg
where
(Select count(distinct seg_airline) from seg s where s.seg_origin_code = seg.seg_origin_code) = @airlineCount
and
(Select count(distinct seg_airline) from seg s where s.seg_destination_code = seg.seg_destination_code) = @airlineCount

If you didn't want it where ALL the airlines had the same origin and destination, only where more than one had the same, you could get rid of the subqueries and do this:

SQL

select distinct s1.seg_airline, s1.seg_origin_code, s1.seg_destination_code from seg s1
inner join seg s2 on s1.seg_origin_code = s2.seg_origin_code and s1.seg_destination_code = s2.seg_destination_code and s1.seg_airline <> s2.seg_airline

You need the distinct, because, by definition, this creates a pair of rows for every match.

Peter Leow · Answer 2 · 2014-01-03T19:28:00

Solution 2

SQL

SELECT A1.SEG_AIRLINE, A1.SEG_ORIGIN_CODE, A1.SEG_DESTINATION_CODE FROM AIRLINE A1
WHERE
(SELECT COUNT(A2.SEG_ORIGIN_CODE+A2.SEG_DESTINATION_CODE) FROM AIRLINE A2
WHERE A1.SEG_ORIGIN_CODE = A2.SEG_ORIGIN_CODE AND A1.SEG_DESTINATION_CODE = A2.SEG_DESTINATION_CODE
GROUP BY A2.SEG_ORIGIN_CODE+A2.SEG_DESTINATION_CODE) > 1

Posted 3-Jan-14 19:28pm

Peter Leow

Comments

Christian Graus 4-Jan-14 1:53am

Good work on combining the two to make only one subquery, but if this is what the OP wanted, it's the opposite of what he asked for ? ( that is, yours will match any group that appears more than once, not for all airlines )