sql server - sql query to get distinct rows in let table in simple two tables

Question

CREATE TABLE [dbo].[tbl_Travel](
    [TE_ID] [int] IDENTITY(1,1) NOT NULL,
    [TRAVEL_TYPE] [varchar](12) NULL,
    [TRAVEL_MODE] [varchar](35) NULL,
    [TRAVEL_CLASS] [nchar](10) NULL)

SET PRIMARY KEY TO TE_ID

INSERT INTO [TAMSMVC].[dbo].[tbl_Travel] VALUES  ('Return', 'Airlines', 'Economy')
INSERT INTO [TAMSMVC].[dbo].[tbl_Travel] VALUES  ('Single', 'Airlines', 'Business')
INSERT INTO [TAMSMVC].[dbo].[tbl_Travel] VALUES  ('Return', 'Airlines', 'Business')
INSERT INTO [TAMSMVC].[dbo].[tbl_Travel] VALUES  ('Single', 'Railway', 'Second')
INSERT INTO [TAMSMVC].[dbo].[tbl_Travel] VALUES  ('Return', 'Railway', 'First')


CREATE TABLE [dbo].[tbl_Journey](
    [JOURNET_ID] [int] IDENTITY(1,1) NOT NULL,
    [TE_ID] [int] NULL,
    [JOURNEY_FROM] [varchar](30) NULL,
    [JOURNEY_TO] [varchar](30) NULL)

SET PRIMARY KEY TO [JOURNET_ID]

INSERT INTO [TAMSMVC].[dbo].[tbl_Journey] VALUES (1,'Mumbai','PUNE')
INSERT INTO [TAMSMVC].[dbo].[tbl_Journey] VALUES (1,'PUNE','Mumbai')
INSERT INTO [TAMSMVC].[dbo].[tbl_Journey] VALUES (2,'BANGALORE','GOA')
INSERT INTO [TAMSMVC].[dbo].[tbl_Journey] VALUES (3,'CHENNAI','PANAJI')
INSERT INTO [TAMSMVC].[dbo].[tbl_Journey] VALUES (3,'PANAJI','CHENNAI')
INSERT INTO [TAMSMVC].[dbo].[tbl_Journey] VALUES (4,'DELHI','KOLKATA')
INSERT INTO [TAMSMVC].[dbo].[tbl_Journey] VALUES (5,'BHOPAL','SHIMALA')
INSERT INTO [TAMSMVC].[dbo].[tbl_Journey] VALUES (5,'SHIMALA','BHOPAL')

AND RESULT SHOULD BE i want distinct rows in the table

Journey_ID  TE_ID   Journey_From    Journey_To  TRAVEL_TYPE [TRAVEL_MODE    TRAVEL_CLASS
  1          1       Mumbai          PUNE         Return      Airlines        Economy   
  3          2       BANGALORE       GOA          Single      Airlines         Business  
  4          3       CHENNAI        PANAJI        Return      Airlines         Business  
  6          4       DELHI          KOLKATA       Single      Railway          Second    
  7          5       BHOPAL         SHIMALA       Return      Railway          First     

AND RESULT SHOULD BE i want distinct rows in the table i want to remove duplicate rows in second table and min it should show min journey_id contained rows

Answer 1

Journey_ID  TE_ID   Journey_From    Journey_To  TRAVEL_TYPE [TRAVEL_MODE    TRAVEL_CLASS    CountofTEID
1   1   Mumbai  PUNE        Return  Airlines    Economy     2
3   2   BANGALORE   GOA         Single  Airlines    Business    1
4   3   CHENNAI PANAJI      Return  Airlines    Business    2
6   4   DELHI   KOLKATA     Single  Railway Second      1
7   5   BHOPAL  SHIMALA     Return  Railway First       2
null    6   null    null    Return  Airlines    Economy     0
null    7   null    null    Return  Airlines    Business    0

In result Iwant all rows from Travel table , count of all journeys and single record with minimum journey_id