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