1

I am unable to get few of the table columns as it requires recursive query which I am not good at. So basically, if its a direct transfer, then remarks section is likely to be null. And if there is a halt in between origin and destination then I need to add the stations to my remarks column.

A to B -> nothing
B to C -> Via B
C -> D -> Via B,C

SQL query is:

CREATE TABLE IPhone (Id int, Country NVARCHAR(12), seqNo int, Send datetime2(0), Arrive datetime2(0));

INSERT INTO IPhone VALUES 
('1001','America','1', '2022-11-23 18:30:00.000',null),
('1002','China','2', '2022-11-24 08:18:00.000','2022-11-24 05:00:00'),
('1003','Argentina','3', '2022-11-25 18:30:00.000','2022-11-24 18:18:00.000'),
('1004','Saudi Arabia','4',null,'2022-11-25 20:30:00.000');

Tried

select  f.id,f.Country CountryFrom, t.Country CountryTo
, convert(varchar(4),f.seqNo) + '-' + convert(varchar(4),t.seqNo) seqNo
, f.Send, t.Arrive,concat('VIA ', f.Country ,', ', t.Country) Remarks from IPhone f inner join IPhone t on f.seqNo < t.seqNo order by id;

Requirement is the following. I tired looking into How to create a MySQL hierarchical recursive query? but I am unbale to get the expected result. Your help is appreciated.

Honey Singh
  • 362
  • 8
  • SQL Server <> MySQL; please only tag the (R)DBMS you are *really* using. – Thom A Nov 28 '22 at 09:19
  • @Larnu noted. Also please kindly help if possible. – Honey Singh Nov 28 '22 at 09:20
  • That depends on what (R)DBMS you are *really* using... Don't "note" the problem, address it and fix the tag(s). – Thom A Nov 28 '22 at 09:20
  • I'm using MSSQL Server. – Honey Singh Nov 28 '22 at 09:22
  • @BhokluSingh then don't use tutorials made for MySQL like the one you have linked. Are you sure you are using MS SQL and not MySQL? – Benny Schärer Nov 28 '22 at 09:25
  • @BennySchärer Yes I am using SQL Server 2016. I am querying my tables using SQL server Management Studio. – Honey Singh Nov 28 '22 at 09:27
  • @BhokluSingh good then have a look on how to actually construct a recursive query. What have you tried so far? What are the requirements other than using a recursive query? I can't dicerne what you are trying to do from just the screenshots you have posted. – Benny Schärer Nov 28 '22 at 09:52
  • @BhokluSingh how to differentiate between a direct flight and halt one? – HariHaravelan Nov 28 '22 at 09:57
  • @HariHaravelan if the seq is 1-2, 2-3, 3-4 then these are direct. .So if we do math here we se all returns 1 (2-1, 3-2, 4-3). . – Honey Singh Nov 28 '22 at 10:02
  • @BennySchärer I posted this question here to ask guys who got the knack of SQL queries so that I can get some ideas on how to get this done. As of now I can only think of Recursive Query. Since Recursive query is quite complicated, therefore I'm seeking help from Gurus. I've also seen your level of query below. Thank you again. – Honey Singh Nov 28 '22 at 10:20

2 Answers2

1

Recursive query is not required, This should work http://sqlfiddle.com/#!18/8843a/1

    SELECT f.id,
       f.country                      CountryFrom,
       t.country                      CountryTo,
       CONVERT(VARCHAR(4), f.seqno) + '-'
       + CONVERT(VARCHAR(4), t.seqno) seqNo,
       f.send,
       t.arrive,
       CASE
         WHEN t.seqno - f.seqno = 1 THEN ''
         ELSE (SELECT 'VIA '
                      + (SELECT LEFT(country, Len(country) - 1)
                         FROM   (SELECT country + ', '
                                 FROM   iphone
                                 WHERE  seqno BETWEEN f.seqno + 1 AND
                                                      t.seqno - 1
                                 FOR xml path ('')) c (country)))
       END                            Remarks
FROM   iphone f
       INNER JOIN iphone t
               ON f.seqno < t.seqno
ORDER  BY id;

enter image description here

HariHaravelan
  • 1,041
  • 1
  • 10
  • 19
-1

If like mentioned in the comments you use MS SQL you can construct recursive queries like so.

with base as (

   <some initial data select>

    UNION ALL

    select ...
    from base
    
)
select ...
Benny Schärer
  • 73
  • 1
  • 7