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_Code1Role_Code2
FI001FI001
FI002FI002
FI004FI004
FI010FI010
FI011FI011
FI012FI012
FI013FI013
FI014FI014
FI015FI015
FI016FI016
FI017FI026
FI024FI027
FI025FI028
FI026FI040
FI027FI043
FI028FI044
FI029FI045
FI030FI046
FI032FI047
FI040FI048
FI043FI049
FI044FI050
FI045FI060
FI046FI061
FI047FI062
FI048FI063
FI049FI064
FI050FI065
FI060FI066
FI061FI067
FI062FI068
FI063FI069
FI064FI070
FI065FI071
FI066FI072
FI067FI073
FI068FI074
FI069FI076
FI070FI077
FI071FI078
FI072FI090
FI073FI091
FI074FI092
FI076FI093
FI077FI094
FI078FI095
FI090FI096
FI091FI097
FI092FI098
FI093FI099
FI094FI100
FI095FI101
FI096FI102
FI097FI104
FI098FI105
FI099FI106
FI100FI107
FI101FI108
FI102FI113
FI103FI114
FI104FI115
FI105FI116
FI106FI118
FI107FI120
FI108FI121
FI109FI130
FI110FI131
FI111FI132
FI113FI133
FI114FI134
FI115FI135
FI116FI136
FI118FI137
FI120FI138
FI121FI142
FI130FI144
FI131FI145
FI132FI146
FI133FI147
FI134FI148
FI135FI149
FI136FI150
FI137FI151
FI138FI152
FI139FI160
FI140FI161
FI141FI162
FI142FI166
FI144FI172
FI145FI180
FI146FI181
FI147FI182
FI148FI191
FI149FI192
FI150FI193
FI151FI194
FI152FI195
FI160FI196
FI161FI197
FI162FI199
FI163FI210
FI164FI211
FI165FI212
FI166FI213
FI167FI214
FI168FI215
FI169FI216
FI170FI217
FI171FI218
FI172FI219
FI180FI220
FI181FI221
FI182FI222
FI191FI223
FI192FI224
FI193FI226
FI194FI227
FI195FI228
FI196FI246
FI197FI258
FI199FI260
FI210FI272
FI211FI275
FI212FI276
FI213FI277
FI214FI278
FI215FI281
FI216FI282
FI217FI283
FI218FI284
FI219FI285
FI220FI286
FI221FI287
FI222FI288
FI223FI289
FI224FI290
FI226FI295
FI227FI298
FI228FI299
FI229FI300
FI240FI303
FI243FI305
FI244FI306
FI245FI307
FI246FI308
FI247FI309
FI248FI310
FI250FI312
FI251FI314
FI252FI315
FI253FI320
FI254FI321
FI257FI324
FI258FI325
FI259FI327
FI260FI329
FI261FI330
FI263FI331
FI266FI332
FI271FI333
FI272FI334
FI273FI336
FI274FI337
FI275FI339
FI276FI340
FI277FI341
FI278FI342
FI279FI343
FI280FI344
FI281FI345
FI282FI348
FI283FI349
FI284FI350
FI285FI352
FI286FI354
FI287FI355
FI288FI356
FI289FI358
FI290FI359
FI291FI360
FI292FI361
FI293FI362
FI294FI363
FI295FI364
FI296FI365
FI297FI366
FI298FI367
FI299FI372
FI300FI373
FI301FI375
FI302FI376
FI303MM001
FI305MM002
FI306MM003
FI307MM007
FI308MM008
FI309MM009
FI310MM010
FI311MM011
FI312MM012
FI313MM014
FI314MM019
FI315MM020
FI317MM021
FI318MM022
FI319MM023
FI320MM025
FI321MM026
FI322MM029
FI323MM030
FI324MM032
FI325MM033
FI326MM034
FI327MM035
FI328MM036
FI329MM037
FI330MM038
FI331MM039
FI332MM043
FI333MM046
FI334MM047
FI335MM048
FI336MM049
FI337MM050
FI338MM051
FI339MM052
FI340MM059
FI341MM060
FI342MM062
FI343MM064
FI344MM065
FI345MM066
FI346MM070
FI347MM071
FI348MM072
FI349MM073
FI350MM075
FI351MM078
FI352PM003
FI353PM004
FI354PM005
FI355PM006
FI356PM007
FI358PM008
FI359PM010
FI360PM011
FI361PM013
FI362PM015
FI363PM016
FI364PM017
FI365PM018
FI366PM020
FI367PM023
FI368PM025
FI369PM026
FI371PM029
FI372PM030
FI373PM031
FI374PM032
FI375PM034
FI376PM036
FI377PM045
FI378PM047
FI379PM048
MM001PM050
MM002PM051
MM003PM052
MM005PM053
MM006PM054
MM007PM055
MM008PM056
MM009PM057
MM010PP005
MM011PP016
MM012PP019
MM014PP033
MM015PP038
MM016PP039
MM018PP040
MM019PP041
MM020PP042
MM021PP043
MM022PP044
MM023PP045
MM025PP046
MM026PP047
MM027PP067
MM029PP071
MM030PP076
MM032PP082
MM033PS002
MM034PS004
MM035PS005
MM036PS006
MM037PS007
MM038RH001
MM039RH002
MM041RH003
MM042RH005
MM043RH007
MM045RH008
MM046RH009
MM047RH010
MM048RH011
MM049RH012
MM050RH013
MM051RH014
MM052RH015
MM054RH016
MM056RH018
MM057RH019
MM058RH020
MM059RH022
MM060RH024
MM061RH025
MM062RH026
MM063RH027
MM064RH028
MM065RH029
MM066RH030
MM070RH031
MM071RH032
MM072RH033
MM073RH034
MM074RH035
MM075RH036
MM076RH037
MM077RH042
MM078RH043
PM001RH044
PM003RH045
PM004RH046
PM005RH048
PM006RH049
PM007RH050
PM008RH051
PM009RH052
PM010RH053
PM011RH054
PM013RH055
PM014RH056
PM015RH057
PM016RH058
PM017RH060
PM018RH061
PM020RH062
PM021RH063
PM022RH065
PM023RH067
PM024RH068
PM025RH070
PM026RH072
PM027RH073
PM029RH074
PM030RH075
PM031RH076
PM032RH077
PM033RH079
PM034RH080
PM035RH081
PM036RH084
PM037RH086
PM038RH087
PM039RH088
PM040RH090
PM041RH091
PM042RH092
PM043RH093
PM044RH094
PM045RH095
PM046RH096
PM047SD001
PM048SD002
PM049SD004
PM050SD006
PM051SD007
PM052SD008
PM053SD009
PM054SD010
PM055SD012
PM056SD013
PM057SD014
PP001SD015
PP002SD016
PP003SD018
PP004SD019
PP005SD020
PP006SD021
PP007SD022
PP008SD024
PP009SD025
PP010SD026
PP011SD027
PP013SD028
PP015SD029
PP016SD035
PP017SD044
PP018SD048
PP019SD051
PP020SD052
PP021SD053
PP022SD054
PP024SD057
PP025SD060
PP026SD061
PP027SD062
PP028SD064
PP029SD068
PP030SD070
PP031SD071
PP032SD072
PP033SD074
PP034SD075
PP038SD079
PP039SD081
PP040SD085
PP041SD086
PP042SD087
PP043SD089
PP044SD090
PP045SD092
PP046SD094
PP047SD095
PP050SD098
PP051SD099
PP052SD100
PP053SD103
PP054SD118
PP055SD119
PP056SD120
PP057SD122
PP062SD124
PP063SD125
PP064SD129
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