three tables join is not giving desired output

Question

I have three tables:

1: Station_Details (master data table)
2: RF_Details
3: WL_Details

As mention in below image. I need a to take data from all three table in to a output table Master data from Station_details and other data from RF and WL tables. If RF_Details and WL_Details tables are having same station id and same DateTime then in output table both rows details will show in one row. If DateTime are different then it will appear in different rows.

I tried this sql query but I am not getting the same output like OUTPUT Table.

  select rf.StationID, st.stationname, st.state,rf.rf,rf.cum-rf,wl.wl,DataTime 
     from  [RF_Details] rf
     join [WL_Details] wl
    join Station_Details st
    on rf.StationID = wl.StationId and
       rf.DataRecieved=wl.DataRecieved and
       st.stationid =rf.stationid and
       st.stationid = wl.stationid;

But it didn't give the right number of rows and output. Please help me for the same.

Answer 1

declare @station_details table(id int, station_id varchar(10),station_name varchar(10),state varchar(10))
declare @rf_details table (id int, station_id varchar(10),rf int, cum_rf int, dt dateTIME)
declare @wl_details table (id int, station_id varchar(10),wl int,dt datetime)

insert into @station_details values
(1,'DEL-NDL','NDL','DEL'),
(2,'UP-LKO','LKO','UP'),
(3,'MP-BHP','BHP','MP'),
(4,'MHR-MUM','MUM','MHR')

INSERT INTO @RF_DETAILS VALUES
(1,'DEL-NDL',42,435,'2016-06-13 05:15:00'),
(2,'UP-LKO',0,501,'2016-06-13 05:15:00'),
(3,'MP-BHP',20,350,'2016-06-13 05:15:00'),
(4,'MHR-MUM',30,200,'2016-06-13 05:15:00'),
(5,'MHR-MUM',15,100,'2016-06-14 05:15:00'),
(6,'UP-LKO',50,350,'2016-06-13 05:15:00')

INSERT INTO @WL_DETAILS VALUES
(1,'DEL-NDL',25,'2016-06-13 05:15:00'),
(2,'UP-LKO',35,'2016-06-13 05:30:00'),
(3,'MP-BHP',46,'2016-06-13 05:45:00'),
(4,'MHR-MUM',20,'2016-06-13 05:15:00'),
(5,'MHR-MUM',15,'2016-06-14 05:15:00'),
(6,'UP-LKO',60,'2016-06-13 05:15:00')

;with cte as
(
SELECT  case 
        when rf.dt = wl.dt then 'Y' 
        else 'N'
        end as matched,
        rf.id as id,rf.station_id as stationid,rf.rf as rf , rf.cum_rf as cumrf , rf.dt as rfdt, 
        wl.id as wlid, wl.station_id ,wl.wl ,wl.dt as wldte, 
        rf.station_id as station,rf.dt as rfdte
FROM    @RF_DETAILS RF
JOIN      @WL_DETAILS WL ON rf.id = wl.id and RF.STATION_ID = WL.STATION_ID
)
select  row_number() over (order by s.id) newid,
        s.id,s.station_id,sd.station_name,sd.state,s.rf,s.cumrf,s.wl,
        case
        when s.srce = 'L' then s.rfdte 
        else s.wldte
        end as 'Date'
from
(
select 'L' as srce,cte.id,cte.station_id,cte.rf,cte.cumrf, cte.wl as wl, cte.rfdte,cte.wldte from cte where cte.matched = 'Y'
union
select 'L' as srce,cte.id,cte.station_id,cte.rf,cte.cumrf, null as wl, cte.rfdte,cte.wldte from cte where cte.matched = 'N'
union all
select 'R' as srce,cte.id * 10,cte.station_id,null,null, cte.wl as wl, cte.rfdte,cte.wldte from cte where cte.matched = 'N'
) s
join    @station_details sd on sd.station_id = s.station_id
order   by s.id

Answer 2

You should redesign your database: right now you have a secondary key in RF_Details and WL_Details - DateTime. Which plays a role of a foreign key between them. Which is no good and will continue confusing you every time you need to join those table or collect corresponding data.

There should be another table like Station_Records which will store a row per every record for that station: (id, station_id, record_date_time). RF and WL rows if any should refer this table instead of referring Station_Details with station_id and one another with datetime.

With current structure you need to do full join of RF and WL to get both: matching by datetime - in same row, not matching - in separate rows.

select sd.station_name, Station_Records.*
from Station_Details sd
inner join
(
  select
    IsNull(rf.station_id, wl.station_id) station_id,
    IsNull(rf.DataRecieved, wl.DataRecieved) DataRecieved,
    rf.rf, rf.cum-rf, wl.wl
  from [RF_Details] rf
  full join [WL_Details] wl
  on wl.station_id = rf.station_id
    and wl.DataRecieved = rf.DataRecieved
) Station_Records
on Station_Records.station_ud = sd.station_id

concrete implementation may consist of OUTER APPLY or even be without any subqueries - it does not really matter currently.

Modify your table structure and you will always know all matching records:

select
  sd.station_id, sd.station_name,
  sr.DataRecieved
  rf.rf, rf.cum-rf,
  wl.wl
from Station_Details sd
inner join Station_Records sr
on sr.station_id = sd.station_id
left join RF_Details rf
on rf.record_id = sr.record_id
left join WL_Details wl
on wl.record_id = sr.record_id

Answer 3

You should always put the join conditions along with the join itself. Also, adding the INNER is a practice I follow to ensure no extra records are returned.

SELECT rf.StationID, st.stationname, st.state, wl.DataRecieved, wl.waterlevel1,
    rf.dailyrainfall, rf.cumrainfall
FROM  [RF_Details] rf
INNER JOIN [WL_Details] wl
ON rf.StationID = wl.StationId AND
   rf.DataRecieved=wl.DataRecieved
INNER JOIN Station_Details st
ON st.stationid =rf.stationid AND
   st.stationid = wl.stationid;