February 23, 2009 at 3:03 pm
Guys,
I have been working on a query to just get the numbers from role_code1 that are not listed in role_code2...I did the following query, and the list is the one below...
Please any idea what I am doing wrong?
Thanks a bunch!.
select role_code1
from temp1 c1
where not exists (select role_code2
from temp1 c2
where c1.role_code1 = c2.role_code1);
Role_Code1 Role_Code2
FI001 FI001
FI002 FI002
FI004 FI004
FI010 FI010
FI011 FI011
FI012 FI012
FI013 FI013
FI014 FI014
FI015 FI015
FI016 FI016
FI017 FI026
FI024 FI027
FI025 FI028
FI026 FI040
FI027 FI043
FI028 FI044
FI029 FI045
FI030 FI046
FI032 FI047
FI040 FI048
FI043 FI049
FI044 FI050
FI045 FI060
FI046 FI061
FI047 FI062
FI048 FI063
FI049 FI064
FI050 FI065
FI060 FI066
FI061 FI067
FI062 FI068
FI063 FI069
FI064 FI070
FI065 FI071
FI066 FI072
FI067 FI073
FI068 FI074
FI069 FI076
FI070 FI077
FI071 FI078
FI072 FI090
FI073 FI091
FI074 FI092
FI076 FI093
FI077 FI094
FI078 FI095
FI090 FI096
FI091 FI097
FI092 FI098
FI093 FI099
FI094 FI100
FI095 FI101
FI096 FI102
FI097 FI104
FI098 FI105
FI099 FI106
FI100 FI107
FI101 FI108
FI102 FI113
FI103 FI114
FI104 FI115
FI105 FI116
FI106 FI118
FI107 FI120
FI108 FI121
FI109 FI130
FI110 FI131
FI111 FI132
FI113 FI133
FI114 FI134
FI115 FI135
FI116 FI136
FI118 FI137
FI120 FI138
FI121 FI142
FI130 FI144
FI131 FI145
FI132 FI146
FI133 FI147
FI134 FI148
FI135 FI149
FI136 FI150
FI137 FI151
FI138 FI152
FI139 FI160
FI140 FI161
FI141 FI162
FI142 FI166
FI144 FI172
FI145 FI180
FI146 FI181
FI147 FI182
FI148 FI191
FI149 FI192
FI150 FI193
FI151 FI194
FI152 FI195
FI160 FI196
FI161 FI197
FI162 FI199
FI163 FI210
FI164 FI211
FI165 FI212
FI166 FI213
FI167 FI214
FI168 FI215
FI169 FI216
FI170 FI217
FI171 FI218
FI172 FI219
FI180 FI220
FI181 FI221
FI182 FI222
FI191 FI223
FI192 FI224
FI193 FI226
FI194 FI227
FI195 FI228
FI196 FI246
FI197 FI258
FI199 FI260
FI210 FI272
FI211 FI275
FI212 FI276
FI213 FI277
FI214 FI278
FI215 FI281
FI216 FI282
FI217 FI283
FI218 FI284
FI219 FI285
FI220 FI286
FI221 FI287
FI222 FI288
FI223 FI289
FI224 FI290
FI226 FI295
FI227 FI298
FI228 FI299
FI229 FI300
FI240 FI303
FI243 FI305
FI244 FI306
FI245 FI307
FI246 FI308
FI247 FI309
FI248 FI310
FI250 FI312
FI251 FI314
FI252 FI315
FI253 FI320
FI254 FI321
FI257 FI324
FI258 FI325
FI259 FI327
FI260 FI329
FI261 FI330
FI263 FI331
FI266 FI332
FI271 FI333
FI272 FI334
FI273 FI336
FI274 FI337
FI275 FI339
FI276 FI340
FI277 FI341
FI278 FI342
FI279 FI343
FI280 FI344
FI281 FI345
FI282 FI348
FI283 FI349
FI284 FI350
FI285 FI352
FI286 FI354
FI287 FI355
FI288 FI356
FI289 FI358
FI290 FI359
FI291 FI360
FI292 FI361
FI293 FI362
FI294 FI363
FI295 FI364
FI296 FI365
FI297 FI366
FI298 FI367
FI299 FI372
FI300 FI373
FI301 FI375
FI302 FI376
FI303 MM001
FI305 MM002
FI306 MM003
FI307 MM007
FI308 MM008
FI309 MM009
FI310 MM010
FI311 MM011
FI312 MM012
FI313 MM014
FI314 MM019
FI315 MM020
FI317 MM021
FI318 MM022
FI319 MM023
FI320 MM025
FI321 MM026
FI322 MM029
FI323 MM030
FI324 MM032
FI325 MM033
FI326 MM034
FI327 MM035
FI328 MM036
FI329 MM037
FI330 MM038
FI331 MM039
FI332 MM043
FI333 MM046
FI334 MM047
FI335 MM048
FI336 MM049
FI337 MM050
FI338 MM051
FI339 MM052
FI340 MM059
FI341 MM060
FI342 MM062
FI343 MM064
FI344 MM065
FI345 MM066
FI346 MM070
FI347 MM071
FI348 MM072
FI349 MM073
FI350 MM075
FI351 MM078
FI352 PM003
FI353 PM004
FI354 PM005
FI355 PM006
FI356 PM007
FI358 PM008
FI359 PM010
FI360 PM011
FI361 PM013
FI362 PM015
FI363 PM016
FI364 PM017
FI365 PM018
FI366 PM020
FI367 PM023
FI368 PM025
FI369 PM026
FI371 PM029
FI372 PM030
FI373 PM031
FI374 PM032
FI375 PM034
FI376 PM036
FI377 PM045
FI378 PM047
FI379 PM048
MM001 PM050
MM002 PM051
MM003 PM052
MM005 PM053
MM006 PM054
MM007 PM055
MM008 PM056
MM009 PM057
MM010 PP005
MM011 PP016
MM012 PP019
MM014 PP033
MM015 PP038
MM016 PP039
MM018 PP040
MM019 PP041
MM020 PP042
MM021 PP043
MM022 PP044
MM023 PP045
MM025 PP046
MM026 PP047
MM027 PP067
MM029 PP071
MM030 PP076
MM032 PP082
MM033 PS002
MM034 PS004
MM035 PS005
MM036 PS006
MM037 PS007
MM038 RH001
MM039 RH002
MM041 RH003
MM042 RH005
MM043 RH007
MM045 RH008
MM046 RH009
MM047 RH010
MM048 RH011
MM049 RH012
MM050 RH013
MM051 RH014
MM052 RH015
MM054 RH016
MM056 RH018
MM057 RH019
MM058 RH020
MM059 RH022
MM060 RH024
MM061 RH025
MM062 RH026
MM063 RH027
MM064 RH028
MM065 RH029
MM066 RH030
MM070 RH031
MM071 RH032
MM072 RH033
MM073 RH034
MM074 RH035
MM075 RH036
MM076 RH037
MM077 RH042
MM078 RH043
PM001 RH044
PM003 RH045
PM004 RH046
PM005 RH048
PM006 RH049
PM007 RH050
PM008 RH051
PM009 RH052
PM010 RH053
PM011 RH054
PM013 RH055
PM014 RH056
PM015 RH057
PM016 RH058
PM017 RH060
PM018 RH061
PM020 RH062
PM021 RH063
PM022 RH065
PM023 RH067
PM024 RH068
PM025 RH070
PM026 RH072
PM027 RH073
PM029 RH074
PM030 RH075
PM031 RH076
PM032 RH077
PM033 RH079
PM034 RH080
PM035 RH081
PM036 RH084
PM037 RH086
PM038 RH087
PM039 RH088
PM040 RH090
PM041 RH091
PM042 RH092
PM043 RH093
PM044 RH094
PM045 RH095
PM046 RH096
PM047 SD001
PM048 SD002
PM049 SD004
PM050 SD006
PM051 SD007
PM052 SD008
PM053 SD009
PM054 SD010
PM055 SD012
PM056 SD013
PM057 SD014
PP001 SD015
PP002 SD016
PP003 SD018
PP004 SD019
PP005 SD020
PP006 SD021
PP007 SD022
PP008 SD024
PP009 SD025
PP010 SD026
PP011 SD027
PP013 SD028
PP015 SD029
PP016 SD035
PP017 SD044
PP018 SD048
PP019 SD051
PP020 SD052
PP021 SD053
PP022 SD054
PP024 SD057
PP025 SD060
PP026 SD061
PP027 SD062
PP028 SD064
PP029 SD068
PP030 SD070
PP031 SD071
PP032 SD072
PP033 SD074
PP034 SD075
PP038 SD079
PP039 SD081
PP040 SD085
PP041 SD086
PP042 SD087
PP043 SD089
PP044 SD090
PP045 SD092
PP046 SD094
PP047 SD095
PP050 SD098
PP051 SD099
PP052 SD100
PP053 SD103
PP054 SD118
PP055 SD119
PP056 SD120
PP057 SD122
PP062 SD124
PP063 SD125
PP064 SD129
PP065
PP066
PP067
PP068
PP069
PP070
PP071
PP072
PP073
PP074
PP075
PP076
PP077
PP078
PP079
PP080
PP081
PP082
PS001
PS002
PS004
PS005
PS006
PS007
PS008
PS010
PS011
PS012
PS013
PS014
PS015
PS016
PS017
PS018
PS019
PS020
PS021
PS022
PS023
PS024
PS025
PS026
PS027
PS028
PS029
PS030
PS031
PS032
PS033
PS034
PS038
RH001
RH002
RH003
RH004
RH005
RH007
RH008
RH009
RH010
RH011
RH012
RH013
RH014
RH015
RH016
RH017
RH018
RH019
RH020
RH021
RH022
RH024
RH025
RH026
RH027
RH028
RH029
RH030
RH031
RH032
RH033
RH034
RH035
RH036
RH037
RH040
RH041
RH042
RH043
RH044
RH045
RH046
RH047
RH048
RH049
RH050
RH051
RH052
RH053
RH054
RH055
RH056
RH057
RH058
RH059
RH060
RH061
RH062
RH063
RH065
RH066
RH067
RH068
RH069
RH070
RH071
RH072
RH073
RH074
RH075
RH076
RH077
RH079
RH080
RH081
RH083
RH084
RH086
RH087
RH088
RH090
RH091
RH092
RH093
RH094
RH095
RH096
RH097
SD001
SD002
SD004
SD005
SD006
SD007
SD008
SD009
SD010
SD012
SD013
SD014
SD015
SD016
SD017
SD018
SD019
SD020
SD021
SD022
SD023
SD024
SD025
SD026
SD027
SD028
SD029
SD030
SD031
SD032
SD035
SD043
SD044
SD045
SD046
SD048
SD049
SD050
SD051
SD052
SD053
SD054
SD055
SD056
SD057
SD060
SD061
SD062
SD063
SD064
SD068
SD069
SD070
SD071
SD072
SD073
SD074
SD075
SD077
SD079
SD081
SD085
SD086
SD087
SD088
SD089
SD090
SD092
SD093
SD094
SD095
SD096
SD097
SD098
SD099
SD100
SD101
SD102
SD103
SD107
SD108
SD109
SD111
SD112
SD113
SD114
SD116
SD117
SD118
SD119
SD120
SD121
SD122
SD123
SD124
SD125
SD127
SD129
SD134
SD135
SD136
February 23, 2009 at 3:24 pm
That should work.
My guess would be that something is different/wrong with the content of the two columns. Please post the CREATE TABLE definition for the table and some INSERTs of the data so that we can test it.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 23, 2009 at 3:29 pm
Barry I am working with Access, what I did was just open my excel document from access wizard, and then wnet over SQL View and run that query...Am i doing wrong?
February 23, 2009 at 4:41 pm
Again, from what you have showed us, it looks OK. Meaning that the problem is probably in the data. You can attach the Excel file here is that is easier for you.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 23, 2009 at 4:43 pm
Hmm, you should also try this query, just to be sure:
Select * from temp1
Dose that return your data?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 23, 2009 at 5:51 pm
Yes, this returned data.
Attached my excel doc.
February 23, 2009 at 6:00 pm
Hi.
If I have your requirements correct then I think you are after:
select role_code1
from temp1
where role_code1 not in (select role_code2 from temp1)
That provides 240 results for me.... Every role_code1 that is not in role_code2.
B
February 23, 2009 at 6:37 pm
Thank you so much, but although the query you are giving me is the one I am looking for, I am running it and nothing shows up. just appear in blank no records....do you know why this is happening?..looks so easy.
Please advise.
Thanks.
February 23, 2009 at 6:40 pm
I am attaching the query results.
February 23, 2009 at 6:59 pm
Hi,
The problem here is that the access syntax is slightly limited.... But i got this working.
SELECT temp.*, temp_1.*
FROM temp LEFT JOIN temp AS temp_1 ON temp.role_code1 = temp_1.role_code2
WHERE (((temp_1.ID) Is Null));
B
February 23, 2009 at 7:44 pm
Thanks Bevan, it worked!!!.......but I am still hitting my head trying to know where was not working, I tried many ways but none showed me results...
Thanks!!!!
February 24, 2009 at 12:27 am
You can try this (small modification to the above query),
select distinct Role_Code1 from temp
where Role_Code1 not in
( select Distinct Role_Code2 from Temp where Role_Code2 is not NULL)
probably b'cos of null you are not getting any results.
Regards,
Ramu
Ramu
No Dream Is Too Big....!
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply