Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2012
    Posts
    16

    Unanswered: SQL to retrieve exact pair

    Hi,
    Consider the tables and data which is attached in the image.
    I want to know the sql which can help me to find out the flightrouteID
    containing only/exactly the following pair:

    FlightID SequenceIndex
    ==== ==========
    F1 1
    F5 2

    It should return only Fr4.
    It should not return Fr1, Fr6, Fr7 even they contain the above pair. Because they contain more than above pair.
    Thanks.
    Attached Thumbnails Attached Thumbnails TablesData.jpg  

  2. #2
    Join Date
    Jan 2012
    Posts
    84
    It would be helpful if you kindly did your homework and provided instructions for create tables and fill them with sample data,
    instead of attaching pretty looking images. Just like this:
    Code:
    CREATE TABLE bridge AS
    SELECT 'Fr1' FlightRouteID , 'F1' FlightID, 1 SequenceIndex FROM dual UNION
    SELECT 'Fr1', 'F1', 1 FROM dual UNION
    SELECT 'Fr2', 'F1', 1 FROM dual UNION
    SELECT 'Fr2', 'F8', 2 FROM dual UNION
    SELECT 'Fr3', 'F2 ', 1 FROM dual UNION
    SELECT 'Fr3', 'F8', 2 FROM dual UNION
    SELECT 'Fr4', 'F1', 1 FROM dual UNION
    SELECT 'Fr4', 'F5', 2 FROM dual UNION
    SELECT 'Fr5', 'F1', 1 FROM dual UNION
    SELECT 'Fr5', 'F5', 2 FROM dual UNION
    SELECT 'Fr5', 'F5', 2 FROM dual UNION
    SELECT 'Fr5', 'F6', 3 FROM dual UNION
    SELECT 'Fr6', 'F1', 1 FROM dual UNION
    SELECT 'Fr6', 'F5', 2 FROM dual UNION
    SELECT 'Fr6', 'F6', 3 FROM dual UNION
    SELECT 'Fr6', 'F7', 4 FROM dual UNION
    SELECT 'Fr7', 'F1', 1 FROM dual UNION
    SELECT 'Fr7', 'F5', 2 FROM dual UNION
    SELECT 'Fr7', 'F8', 3 FROM dual 
    ;
    Having sample data people can easy recreate and play with your case on their computers and give you back working solutions,
    but not many would be willing to tiresomly type char by char from images.

    One possible option (probably not the fastest one) might be this query:
    Code:
    WITH pair AS(
      SELECT 'F1' as FlightID, 1 as SequenceIndex FROM dual UNION 
      SELECT 'F5', 2 FROM dual
    )
    SELECT b.FlightRouteID
    FROM bridge b
    LEFT JOIN pair p
    ON  ( b.SequenceIndex = p.SequenceIndex AND b.FlightID = p.FlightID )
    GROUP BY b.FlightRouteID
    HAVING count( CASE WHEN p.flightid IS NULL THEN 1 END ) = 0
       AND count(*) = ( SELECT count(*) FROM pair )
    ;
    
    FLIGHTROUTEID
    -------------
    Fr4

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    This problem is known as Exact Relational Division(or Relational Division without remainder).
    Search with those keywords.

  4. #4
    Join Date
    Jul 2012
    Posts
    16
    Thanks for the wonderful solution. :-).
    I will try to follow your suggestion regarding a new post @kordirko.
    Thanks to @tonkuma for telling me the name of the problem.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •