Forum Discussion

Neha11's avatar
Neha11
Copper Contributor
Feb 05, 2023

Sql query

Hi Everyone, I have one sql query to be solved as practice, have attached the ss Output: need to select the src and dest that appears only ones Delhi Mum Delhi Kolkata Mum Nagpur   I have solv...
sql query
olafhelper's avatar
olafhelper
Bronze Contributor
Feb 06, 2023
That's really to less on information.
Please post table design as DDL, some sample data as DML statement and the expected result.
  • Neha11's avatar
    Neha11
    Copper Contributor
    Feb 16, 2023
    Hi,
    Here is the script
    Create table New_Airdata(src,dest)
    Insert into New_Airdata(src,dest) values('Delhi','Mum');
    Insert into New_Airdata(src,dest) values('Mum','Delhi');
    Insert into New_Airdata(src,dest) values('Delhi','Kolkata');
    Insert into New_Airdata(src,dest) values('Kolkata','Delhi');
    Insert into New_Airdata(src,dest) values('Mum','Nagpur');

    Output:
    Src. Dest
    Delhi. Mum
    Delhi. Kolkata
    Mum. Nagpur

    I have tried using lead function, any other ways that we can query this?
    • SnowMan55's avatar
      SnowMan55
      Bronze Contributor
      Feb 17, 2023

      Neha11 

      Your table definition probably should specify that neither src nor dest can be NULL.

       

      I have not tested it, but I think you want something like this:

      SELECT T1.src, T1.dest
FROM New_Airdata AS T1
WHERE T1.src < T1.dest
   OR NOT EXISTS
    -- a row with swapped src and dest values
    (SELECT *
     FROM New_Airdata AS T2
     WHERE T2.src=T1.dest
       AND T2.dest = T1.src
    )
--[possibly also sorting the results:]
ORDER BY T1.src, T1.dest

      Alternatively,

      WHERE T1.src > T1.dest

      should give you the same routes, in reverse.

      I'll assume you have no rows where src and dest have the same values - a nonsensical route.

       

       

Resources