Home

SQL JOIN QUERY - RESULT

%3CLINGO-SUB%20id%3D%22lingo-sub-1062164%22%20slang%3D%22en-US%22%3ESQL%20JOIN%20QUERY%20-%20RESULT%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1062164%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20trying%20to%20join%20the%20below%20two%20queries%20but%20not%20getting%20the%20accurate%20result.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eselect%3CBR%20%2F%3Ebi.CONSUMERNO%2C%3CBR%20%2F%3Ebi.UNITCONSUMED%2C%20--%20billed%20unit%3CBR%20%2F%3Ebi.BILLDATE%3CBR%20%2F%3Efrom%3CBR%20%2F%3Etbl_billinfo%20bi%3CBR%20%2F%3Ewhere%20bi.CONSUMERNO%20%3D%20'120400824271'%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAND%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eselect%20%5BTBL_PAYMENTSETTLEMENT%5D.CONSUMERNO%2C%20%5Bdbo%5D.%5BTBL_PAYMENTSETTLEMENT%5D.CURRENT_EC%2C%3CBR%20%2F%3E%5Bdbo%5D.%5BTBL_PAYMENTSETTLEMENT%5D.PAYMENTDATETIME%20from%20%5BTBL_PAYMENTSETTLEMENT%5D%20where%3CBR%20%2F%3E%5BTBL_PAYMENTSETTLEMENT%5D.CONSUMERNO%20%3D%20'120400824271'%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERESULT%20I%20NEED%20%3A%20unique%20bill%20date%20%2C%20billed%20unit%2C%20current_ec%20and%20paymentdatetime%20of%20this%20consumer%3C%2FP%3E%3CP%3E%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EQUERY%20I%20TRIED%20USING%20JOIN%20%3A%3C%2FP%3E%3CP%3E%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3C%2FP%3E%3CP%3Eselect%3CBR%20%2F%3E%5Bdbo%5D.%5BTBL_BILLINFO%5D.CONSUMERNO%2C%3CBR%20%2F%3E%5Bdbo%5D.%5BTBL_BILLINFO%5D.UNITCONSUMED%2C%20--%20billed%20unit%3CBR%20%2F%3E%5Bdbo%5D.%5BTBL_BILLINFO%5D.BILLDATE%2C%3CBR%20%2F%3E%5Bdbo%5D.%5BTBL_PAYMENTSETTLEMENT%5D.CURRENT_EC%2C%3CBR%20%2F%3E%5Bdbo%5D.%5BTBL_PAYMENTSETTLEMENT%5D.PAYMENTDATETIME%3CBR%20%2F%3Efrom%3CBR%20%2F%3Etbl_billinfo%3CBR%20%2F%3Eright%20join%3CBR%20%2F%3E%5BTBL_PAYMENTSETTLEMENT%5D%3CBR%20%2F%3Eon%3CBR%20%2F%3Etbl_billinfo.CONSUMERNO%20%3D%20%5Bdbo%5D.%5BTBL_PAYMENTSETTLEMENT%5D.CONSUMERNO%3CBR%20%2F%3Ewhere%3CBR%20%2F%3E%5BTBL_BILLINFO%5D.CONSUMERNO%20%3D%20'120400824271'%3CBR%20%2F%3Egroup%20by%3CBR%20%2F%3E%5Bdbo%5D.%5BTBL_BILLINFO%5D.CONSUMERNO%2C%3CBR%20%2F%3E%5Bdbo%5D.%5BTBL_BILLINFO%5D.UNITCONSUMED%2C%20--%20billed%20unit%3CBR%20%2F%3E%5Bdbo%5D.%5BTBL_BILLINFO%5D.BILLDATE%2C%3CBR%20%2F%3E%5Bdbo%5D.%5BTBL_PAYMENTSETTLEMENT%5D.CURRENT_EC%2C%3CBR%20%2F%3E%5Bdbo%5D.%5BTBL_PAYMENTSETTLEMENT%5D.PAYMENTDATETIME%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBELOW%20ARE%20THE%20OUTPUT%20OF%20BOTH%20QUERIES%3A%3C%2FP%3E%3CP%3E%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eselect%3CBR%20%2F%3Ebi.CONSUMERNO%2C%3CBR%20%2F%3Ebi.UNITCONSUMED%2C%20--%20billed%20unit%3CBR%20%2F%3Ebi.BILLDATE%3CBR%20%2F%3Efrom%3CBR%20%2F%3Etbl_billinfo%20bi%3CBR%20%2F%3Ewhere%20bi.CONSUMERNO%20%3D%20'120400824271'%3C%2FP%3E%3CP%3E120400824271%2093.00%202014-12-15%3CBR%20%2F%3E120400824271%2094.00%202015-01-15%3CBR%20%2F%3E120400824271%2095.00%202015-02-11%3CBR%20%2F%3E120400824271%2094.00%202015-03-09%3CBR%20%2F%3E120400824271%2094.00%202015-04-14%3CBR%20%2F%3E120400824271%2094.00%202015-05-15%3CBR%20%2F%3E120400824271%2094.00%202015-06-08%3CBR%20%2F%3E120400824271%2094.00%202015-07-09%3CBR%20%2F%3E120400824271%2094.00%202015-08-08%3CBR%20%2F%3E120400824271%2094.00%202015-09-09%3CBR%20%2F%3E120400824271%2094.00%202015-10-08%3CBR%20%2F%3E120400824271%2094.00%202015-11-10%3CBR%20%2F%3E120400824271%2094.00%202015-12-11%3CBR%20%2F%3E120400824271%2094.00%202016-01-11%3CBR%20%2F%3E120400824271%2094.00%202016-02-07%3CBR%20%2F%3E120400824271%2094.00%202016-03-08%3CBR%20%2F%3E120400824271%2094.00%202016-04-10%3CBR%20%2F%3E120400824271%2094.00%202016-05-08%3CBR%20%2F%3E120400824271%2094.00%202016-06-10%3CBR%20%2F%3E120400824271%2094.00%202016-07-11%3CBR%20%2F%3E120400824271%2094.00%202016-08-09%3CBR%20%2F%3E120400824271%2094.00%202016-09-10%3CBR%20%2F%3E120400824271%2094.00%202016-10-09%3CBR%20%2F%3E120400824271%2094.00%202016-11-08%3CBR%20%2F%3E120400824271%2094.00%202016-12-07%3CBR%20%2F%3E120400824271%2094.00%202017-01-10%3CBR%20%2F%3E120400824271%2094.00%202017-02-09%3CBR%20%2F%3E120400824271%2094.00%202017-03-06%3CBR%20%2F%3E120400824271%2094.00%202017-04-12%3CBR%20%2F%3E120400824271%2094.00%202017-05-09%3CBR%20%2F%3E120400824271%20130.00%202017-06-13%3CBR%20%2F%3E120400824271%20130.00%202017-07-10%3CBR%20%2F%3E120400824271%20130.00%202017-08-14%3CBR%20%2F%3E120400824271%20130.00%202017-09-15%3CBR%20%2F%3E120400824271%20130.00%202017-10-17%3CBR%20%2F%3E120400824271%20130.00%202017-11-17%3CBR%20%2F%3E120400824271%20130.00%202017-12-16%3CBR%20%2F%3E120400824271%20130.00%202018-01-15%3CBR%20%2F%3E120400824271%20130.00%202018-02-12%3CBR%20%2F%3E120400824271%20130.00%202018-03-09%3CBR%20%2F%3E120400824271%20130.00%202018-04-20%3CBR%20%2F%3E120400824271%20142.00%202018-05-12%3CBR%20%2F%3E120400824271%20142.00%202018-06-12%3CBR%20%2F%3E120400824271%20142.00%202018-07-12%3CBR%20%2F%3E120400824271%20142.00%202018-08-11%3CBR%20%2F%3E120400824271%20142.00%202018-09-09%3CBR%20%2F%3E120400824271%20142.00%202018-10-11%3CBR%20%2F%3E120400824271%20142.00%202018-11-09%3CBR%20%2F%3E120400824271%20142.00%202018-12-11%3CBR%20%2F%3E120400824271%20142.00%202019-01-07%3CBR%20%2F%3E120400824271%20142.00%202019-02-05%3CBR%20%2F%3E120400824271%20142.00%202019-03-03%3CBR%20%2F%3E120400824271%20142.00%202019-04-17%3CBR%20%2F%3E120400824271%20142.00%202019-05-16%3CBR%20%2F%3E120400824271%2048.00%202019-06-21%3CBR%20%2F%3E120400824271%20142.00%202019-07-15%3CBR%20%2F%3E120400824271%20142.00%202019-08-10%3CBR%20%2F%3E120400824271%20142.00%202019-09-15%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CBR%20%2F%3Eselect%20%5BTBL_PAYMENTSETTLEMENT%5D.CONSUMERNO%2C%20%5Bdbo%5D.%5BTBL_PAYMENTSETTLEMENT%5D.CURRENT_EC%2C%5Bdbo%5D.%5BTBL_PAYMENTSETTLEMENT%5D.PAYMENTDATETIME%20from%20%5BTBL_PAYMENTSETTLEMENT%5D%20where%5BTBL_PAYMENTSETTLEMENT%5D.CONSUMERNO%20%3D%20'120400824271'%3C%2FP%3E%3CP%3E%3CBR%20%2F%3E120400824271%20291.74%202014-12-23%2000%3A00%3A00.000%3CBR%20%2F%3E120400824271%20311.00%202015-01-20%2000%3A00%3A00.000%3CBR%20%2F%3E120400824271%20266.27%202015-07-30%2000%3A00%3A00.000%3CBR%20%2F%3E120400824271%20300.67%202015-08-28%2000%3A00%3A00.000%3CBR%20%2F%3E120400824271%20329.80%202015-09-24%2000%3A00%3A00.000%3CBR%20%2F%3E120400824271%20329.80%202015-10-26%2000%3A00%3A00.000%3CBR%20%2F%3E120400824271%20329.80%202015-11-17%2000%3A00%3A00.000%3CBR%20%2F%3E120400824271%20329.80%202016-01-21%2000%3A00%3A00.000%3CBR%20%2F%3E120400824271%20329.80%202016-02-29%2000%3A00%3A00.000%3CBR%20%2F%3E120400824271%20329.80%202016-03-16%2000%3A00%3A00.000%3CBR%20%2F%3E120400824271%20329.80%202016-05-30%2000%3A00%3A00.000%3CBR%20%2F%3E120400824271%20329.80%202016-06-19%2000%3A00%3A00.000%3CBR%20%2F%3E120400824271%20273.53%202016-07-20%2000%3A00%3A00.000%3CBR%20%2F%3E120400824271%20329.80%202016-08-31%2000%3A00%3A00.000%3CBR%20%2F%3E120400824271%20329.80%202016-10-23%2000%3A00%3A00.000%3CBR%20%2F%3E120400824271%20329.80%202016-12-29%2000%3A00%3A00.000%3CBR%20%2F%3E120400824271%20329.80%202017-01-31%2000%3A00%3A00.000%3CBR%20%2F%3E120400824271%20329.80%202017-02-17%2000%3A00%3A00.000%3CBR%20%2F%3E120400824271%20329.80%202017-03-14%2000%3A00%3A00.000%3CBR%20%2F%3E120400824271%200.00%202017-03-29%2000%3A00%3A00.000%3CBR%20%2F%3E120400824271%20289.78%202017-04-30%2000%3A00%3A00.000%3CBR%20%2F%3E120400824271%20334.20%202017-05-19%2000%3A00%3A00.000%3CBR%20%2F%3E120400824271%20482.68%202017-06-30%2000%3A00%3A00.000%3CBR%20%2F%3E120400824271%20337.58%202017-08-28%2000%3A00%3A00.000%3CBR%20%2F%3E120400824271%20489.00%202017-09-27%2000%3A00%3A00.000%3CBR%20%2F%3E120400824271%20489.00%202017-10-26%2000%3A00%3A00.000%3CBR%20%2F%3E120400824271%20489.00%202017-11-25%2000%3A00%3A00.000%3CBR%20%2F%3E120400824271%20489.00%202017-12-27%2000%3A00%3A00.000%3CBR%20%2F%3E120400824271%20481.80%202018-01-31%2000%3A00%3A00.000%3CBR%20%2F%3E120400824271%20481.78%202018-02-28%2000%3A00%3A00.000%3CBR%20%2F%3E120400824271%20489.00%202018-03-17%2000%3A00%3A00.000%3CBR%20%2F%3E120400824271%20489.00%202018-04-26%2000%3A00%3A00.000%3CBR%20%2F%3E120400824271%20481.56%202018-05-26%2000%3A00%3A00.000%3CBR%20%2F%3E120400824271%20481.52%202018-09-22%2000%3A00%3A00.000%3CBR%20%2F%3E120400824271%20481.51%202018-10-26%2000%3A00%3A00.000%3CBR%20%2F%3E120400824271%20540.60%202018-12-22%2000%3A00%3A00.000%3CBR%20%2F%3E120400824271%20540.60%202019-02-15%2000%3A00%3A00.000%3CBR%20%2F%3E120400824271%20540.60%202019-03-29%2000%3A00%3A00.000%3CBR%20%2F%3E120400824271%20140.00%202019-06-26%2000%3A00%3A00.000%3CBR%20%2F%3E120400824271%20385.18%202019-07-27%2000%3A00%3A00.000%3CBR%20%2F%3E120400824271%2096.30%202019-07-27%2000%3A00%3A00.000%3CBR%20%2F%3E120400824271%2096.25%202019-08-19%2000%3A00%3A00.000%3CBR%20%2F%3E120400824271%2096.26%202019-09-27%2000%3A00%3A00.000%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Abhineet_SISL
Occasional Visitor

I am trying to join the below two queries but not getting the accurate result.

 

select
bi.CONSUMERNO,
bi.UNITCONSUMED, -- billed unit
bi.BILLDATE
from
tbl_billinfo bi
where bi.CONSUMERNO = '120400824271'

 

AND

 

select [TBL_PAYMENTSETTLEMENT].CONSUMERNO, [dbo].[TBL_PAYMENTSETTLEMENT].CURRENT_EC,
[dbo].[TBL_PAYMENTSETTLEMENT].PAYMENTDATETIME from [TBL_PAYMENTSETTLEMENT] where
[TBL_PAYMENTSETTLEMENT].CONSUMERNO = '120400824271'

 

RESULT I NEED : unique bill date , billed unit, current_ec and paymentdatetime of this consumer

==========

 

QUERY I TRIED USING JOIN :

===================

select
[dbo].[TBL_BILLINFO].CONSUMERNO,
[dbo].[TBL_BILLINFO].UNITCONSUMED, -- billed unit
[dbo].[TBL_BILLINFO].BILLDATE,
[dbo].[TBL_PAYMENTSETTLEMENT].CURRENT_EC,
[dbo].[TBL_PAYMENTSETTLEMENT].PAYMENTDATETIME
from
tbl_billinfo
right join
[TBL_PAYMENTSETTLEMENT]
on
tbl_billinfo.CONSUMERNO = [dbo].[TBL_PAYMENTSETTLEMENT].CONSUMERNO
where
[TBL_BILLINFO].CONSUMERNO = '120400824271'
group by
[dbo].[TBL_BILLINFO].CONSUMERNO,
[dbo].[TBL_BILLINFO].UNITCONSUMED, -- billed unit
[dbo].[TBL_BILLINFO].BILLDATE,
[dbo].[TBL_PAYMENTSETTLEMENT].CURRENT_EC,
[dbo].[TBL_PAYMENTSETTLEMENT].PAYMENTDATETIME;

 

 

 

 

 

BELOW ARE THE OUTPUT OF BOTH QUERIES:

===============================

 

select
bi.CONSUMERNO,
bi.UNITCONSUMED, -- billed unit
bi.BILLDATE
from
tbl_billinfo bi
where bi.CONSUMERNO = '120400824271'

120400824271 93.00 2014-12-15
120400824271 94.00 2015-01-15
120400824271 95.00 2015-02-11
120400824271 94.00 2015-03-09
120400824271 94.00 2015-04-14
120400824271 94.00 2015-05-15
120400824271 94.00 2015-06-08
120400824271 94.00 2015-07-09
120400824271 94.00 2015-08-08
120400824271 94.00 2015-09-09
120400824271 94.00 2015-10-08
120400824271 94.00 2015-11-10
120400824271 94.00 2015-12-11
120400824271 94.00 2016-01-11
120400824271 94.00 2016-02-07
120400824271 94.00 2016-03-08
120400824271 94.00 2016-04-10
120400824271 94.00 2016-05-08
120400824271 94.00 2016-06-10
120400824271 94.00 2016-07-11
120400824271 94.00 2016-08-09
120400824271 94.00 2016-09-10
120400824271 94.00 2016-10-09
120400824271 94.00 2016-11-08
120400824271 94.00 2016-12-07
120400824271 94.00 2017-01-10
120400824271 94.00 2017-02-09
120400824271 94.00 2017-03-06
120400824271 94.00 2017-04-12
120400824271 94.00 2017-05-09
120400824271 130.00 2017-06-13
120400824271 130.00 2017-07-10
120400824271 130.00 2017-08-14
120400824271 130.00 2017-09-15
120400824271 130.00 2017-10-17
120400824271 130.00 2017-11-17
120400824271 130.00 2017-12-16
120400824271 130.00 2018-01-15
120400824271 130.00 2018-02-12
120400824271 130.00 2018-03-09
120400824271 130.00 2018-04-20
120400824271 142.00 2018-05-12
120400824271 142.00 2018-06-12
120400824271 142.00 2018-07-12
120400824271 142.00 2018-08-11
120400824271 142.00 2018-09-09
120400824271 142.00 2018-10-11
120400824271 142.00 2018-11-09
120400824271 142.00 2018-12-11
120400824271 142.00 2019-01-07
120400824271 142.00 2019-02-05
120400824271 142.00 2019-03-03
120400824271 142.00 2019-04-17
120400824271 142.00 2019-05-16
120400824271 48.00 2019-06-21
120400824271 142.00 2019-07-15
120400824271 142.00 2019-08-10
120400824271 142.00 2019-09-15

 


select [TBL_PAYMENTSETTLEMENT].CONSUMERNO, [dbo].[TBL_PAYMENTSETTLEMENT].CURRENT_EC,
[dbo].[TBL_PAYMENTSETTLEMENT].PAYMENTDATETIME from [TBL_PAYMENTSETTLEMENT] where
[TBL_PAYMENTSETTLEMENT].CONSUMERNO = '120400824271'


120400824271 291.74 2014-12-23 00:00:00.000
120400824271 311.00 2015-01-20 00:00:00.000
120400824271 266.27 2015-07-30 00:00:00.000
120400824271 300.67 2015-08-28 00:00:00.000
120400824271 329.80 2015-09-24 00:00:00.000
120400824271 329.80 2015-10-26 00:00:00.000
120400824271 329.80 2015-11-17 00:00:00.000
120400824271 329.80 2016-01-21 00:00:00.000
120400824271 329.80 2016-02-29 00:00:00.000
120400824271 329.80 2016-03-16 00:00:00.000
120400824271 329.80 2016-05-30 00:00:00.000
120400824271 329.80 2016-06-19 00:00:00.000
120400824271 273.53 2016-07-20 00:00:00.000
120400824271 329.80 2016-08-31 00:00:00.000
120400824271 329.80 2016-10-23 00:00:00.000
120400824271 329.80 2016-12-29 00:00:00.000
120400824271 329.80 2017-01-31 00:00:00.000
120400824271 329.80 2017-02-17 00:00:00.000
120400824271 329.80 2017-03-14 00:00:00.000
120400824271 0.00 2017-03-29 00:00:00.000
120400824271 289.78 2017-04-30 00:00:00.000
120400824271 334.20 2017-05-19 00:00:00.000
120400824271 482.68 2017-06-30 00:00:00.000
120400824271 337.58 2017-08-28 00:00:00.000
120400824271 489.00 2017-09-27 00:00:00.000
120400824271 489.00 2017-10-26 00:00:00.000
120400824271 489.00 2017-11-25 00:00:00.000
120400824271 489.00 2017-12-27 00:00:00.000
120400824271 481.80 2018-01-31 00:00:00.000
120400824271 481.78 2018-02-28 00:00:00.000
120400824271 489.00 2018-03-17 00:00:00.000
120400824271 489.00 2018-04-26 00:00:00.000
120400824271 481.56 2018-05-26 00:00:00.000
120400824271 481.52 2018-09-22 00:00:00.000
120400824271 481.51 2018-10-26 00:00:00.000
120400824271 540.60 2018-12-22 00:00:00.000
120400824271 540.60 2019-02-15 00:00:00.000
120400824271 540.60 2019-03-29 00:00:00.000
120400824271 140.00 2019-06-26 00:00:00.000
120400824271 385.18 2019-07-27 00:00:00.000
120400824271 96.30 2019-07-27 00:00:00.000
120400824271 96.25 2019-08-19 00:00:00.000
120400824271 96.26 2019-09-27 00:00:00.000

 

 

Related Conversations
Using SQL query with column as parameter?
anuyen in Excel on
3 Replies
Sorting problem with imported data from query
DenisL in Excel on
5 Replies
Excel web query DataFormat.Error "not a valid path"
Simon Pearce in Excel on
7 Replies
Counting Days
Tim Hunter in SQL Server on
2 Replies
SQL Server 2016 crashes
mrktos in SQL Server on
0 Replies