How avoid duplicate rows in sql quries using join

Posted by Cpatil1000 under Sql Server on 9/30/2014 | Points: 10 | Views : 590 | Status : [Member] | Replies : 4
Hi,
I have two table first is URBAN_MPR_ANNEXURE_1A with matching table URBAN_MPR_SCHEME_MASTER on scheme id. Actual i want exists and not exists of rows of data. In My Table URBAN_MPR_ANNEXURE_1A have 6 rows enter a data and my URBAN_MPR_SCHEME_MASTER in table have 18 rows for scheme.
Now want if scheme is exist in table URBAN_MPR_ANNEXURE_1A list with not exist scheme from scheme of table URBAN_MPR_SCHEME_MASTER. I have try add extra column then it is asking grouping and rows is increasing. SO i Want also avoid duplicate records. I want only display 18 rows if scheme exist or not exist list. May be my query is wrong. Or any query how I get exists and not exist record for data table.

Table : URBAN_MPR_ANNEXURE_1A
MONTH DIVISION_ID SCHEME_ID NAME_OF_SCHEMES ETP_CHARGED GROSS_COST
4 11 103 IMP. TO NAVAPUR WSS 29.21 196.15
4 11 104 POLLUTION ABATEMENT WORKS ON TAPI RIVER AT PRAKASHA 18.11 244.43
4 11 116 PAROLA WSS 0.00 403.32
4 11 165 SHRI BHAUSAHEB HIRE GOVT. MEDICAL COLLEGE DHULE 20.53 137.81
4 11 173 AUG. TO DHARANGAON WSS 209.42 1406.08
4 11 175 AUG. TO YAWAL WSS 20.66 173.71
Table : URBAN_MPR_SCHEME_MASTER
ID DIVISION_ID SCHEME
103 11 Imp. to Navapur WSS
104 11 Pollution abatement works on Tapi river at Prakasha
116 11 Parola WSS
165 11 Shri Bhausaheb Hire Govt. Medical College Dhule
173 11 Aug. to Dharangaon WSS
175 11 Aug. to Yawal WSS
178 11 Savada Sullage Management Scheme
179 11 Aug. to Erandol WSS Dist. Jalgaon
196 11 Dhule (Tapi River as a source) WSS
197 11 Construction of ESR in Khandesh mill area of Chalisgaon DIST. Jalgaon
201 11 Extn. to Amalner WSS Dist. Jalgaon
206 11 CHOPDA WSS STAGE II DIST. JALGAON
207 11 AUG. TO FAIZPUR WSS Tq Yawal Dist. Jalgaon
214 11 Pachora WSS Dist. Jalgaon
226 11 Int. Relief to Nandurbar WSS Dist. Nandurbar
228 11 Aug. to Raver WSS Dist. Jalgaon
229 11 Aug. to Savada WSS Tq. Raver Dist. Jalgaon
262 11 Jawaher Navoday Vidhalay Dhule

Quries..


Select DISTINCT A.MONTH, 'NAME_OF_SCHEMES' = (Case When A.SCHEME_ID = B.ID Then Upper(A.NAME_OF_SCHEMES)
Else Upper(B.SCHEME) End),
'ETP_CHARGED' = (Case When A.SCHEME_ID = B.ID Then Sum(A.ETP_CHARGED)
Else 0 End),
'GROSS_COST' = (Case When A.SCHEME_ID = B.ID Then Sum(A.GROSS_COST)
Else 0 End)
From URBAN_MPR_ANNEXURE_1A A, URBAN_MPR_SCHEME_MASTER B
Where A.DIVISION_ID = 11 AND A.MONTH = 4 AND B.DIVISION_ID = 11
Group By MONTH,A.SCHEME_ID, B.ID, A.NAME_OF_SCHEMES, B.SCHEME

Output :
MONTH NAME_OF_SCHEMES ETP_CHARGED GROSS_COST
4 AUG. TO DHARANGAON WSS 0.00 0.00
4 AUG. TO DHARANGAON WSS 209.42 1406.08
4 AUG. TO ERANDOL WSS DIST. JALGAON 0.00 0.00
4 AUG. TO FAIZPUR WSS TQ YAWAL DIST. JALGAON 0.00 0.00
4 AUG. TO RAVER WSS DIST. JALGAON 0.00 0.00
4 AUG. TO SAVADA WSS TQ. RAVER DIST. JALGAON 0.00 0.00
4 AUG. TO YAWAL WSS 0.00 0.00
4 AUG. TO YAWAL WSS 20.66 173.71
4 CHOPDA WSS STAGE II DIST. JALGAON 0.00 0.00
4 CONSTRUCTION OF ESR IN KHANDESH MILL AREA OF CHALISGAON DIST. JALGAON 0.00 0.00
4 DHULE (TAPI RIVER AS A SOURCE) WSS 0.00 0.00
4 EXTN. TO AMALNER WSS DIST. JALGAON 0.00 0.00
4 IMP. TO NAVAPUR WSS 0.00 0.00
4 IMP. TO NAVAPUR WSS 29.21 196.15
4 INT. RELIEF TO NANDURBAR WSS DIST. NANDURBAR 0.00 0.00
4 JAWAHER NAVODAY VIDHALAY DHULE 0.00 0.00
4 PACHORA WSS DIST. JALGAON 0.00 0.00
4 PAROLA WSS 0.00 0.00
4 PAROLA WSS 0.00 403.32
4 POLLUTION ABATEMENT WORKS ON TAPI RIVER AT PRAKASHA 0.00 0.00
4 POLLUTION ABATEMENT WORKS ON TAPI RIVER AT PRAKASHA 18.11 244.43
4 SAVADA SULLAGE MANAGEMENT SCHEME 0.00 0.00
4 SHRI BHAUSAHEB HIRE GOVT. MEDICAL COLLEGE DHULE 0.00 0.00
4 SHRI BHAUSAHEB HIRE GOVT. MEDICAL COLLEGE DHULE 20.53 137.81




Responses

Posted by: Bandi on: 9/30/2014 [Member] [MVP] Platinum | Points: 25

Up
0
Down
could you please post the data of those two tables and expected output?

i didn't understand the query explanation. please post the expected output along with sample data

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Cpatil1000, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Naveenhcl on: 9/30/2014 [Member] Starter | Points: 25

Up
0
Down
Hi,

Can you please elaborate your requirement as per Microsoft terminology for getting more responses from our end.

Cpatil1000, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 9/30/2014 [Member] [MVP] Platinum | Points: 25

Up
0
Down
May be this is what you are looking for ......


Select DISTINCT A.MONTH, 'NAME_OF_SCHEMES' = ISNULL(Upper(A.NAME_OF_SCHEMES) , Upper(B.SCHEME)),
'ETP_CHARGED' = SUM(Case When A.SCHEME_ID = B.ID Then A.ETP_CHARGED Else 0 End),
'GROSS_COST' = SUM(Case When A.SCHEME_ID = B.ID Then A.GROSS_COST Else 0 End)
From URBAN_MPR_ANNEXURE_1A A
RIGHT JOIN URBAN_MPR_SCHEME_MASTER B ON A.SCHEME_ID = B.ID
Where A.DIVISION_ID = 11 AND A.MONTH = 4 AND B.DIVISION_ID = 11
Group By MONTH, A.NAME_OF_SCHEMES, B.SCHEME


if not, post the expected output for the above sample data.

NOTE: i didn't see any MONTH and Schema_Id columns in your sample data

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Cpatil1000, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Naveenhcl on: 9/30/2014 [Member] Starter | Points: 25

Up
0
Down
Hi,

I think left join is suitable in this case use join and implement your task by using below sample.

select *
from URBAN_MPR_SCHEME_MASTER a
left join URBAN_MPR_ANNEXURE_1A b on a.DIVISION_ID = b.DIVISION_ID


Hope this will helpful to you...

Cpatil1000, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response