#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2009
    Posts
    5
    Rep Power
    0

    SQL Query Problem


    I have a table that has about 20k rows and just including a snippet of the data:

    COMPARISON WHO SVCD SVDESC ADMIT COUNT
    1010 1010 2 MOD A 2
    1010 1010 2 MOD R 0
    1010 NATIONAL 2 MOD A 41296
    1010 NATIONAL 2 MOD R 1909
    1012 1012 2 MOD A 2
    1012 1012 2 MOD R 0
    1012 NATIONAL 2 MOD A 41296
    1012 NATIONAL 2 MOD R 1909
    1013 1013 2 MOD A 2
    1013 1013 2 MOD R 1
    1013 NATIONAL 2 MOD A 41296
    1013 NATIONAL 2 MOD R 1909
    1014 1014 0 N/A A 1
    1014 1014 0 N/A R 0
    1014 NATIONAL 0 N/A A 12495
    1014 NATIONAL 0 N/A R 52
    1015 1015 2 MOD A 2
    1015 1015 2 MOD R 0
    1015 NATIONAL 2 MOD A 41296
    1015 NATIONAL 2 MOD R 1909
    1016 1016 3 MAJ A 9
    1016 1016 3 MAJ R 1
    1016 NATIONAL 3 MAJ A 7592
    1016 NATIONAL 3 MAJ R 863
    1017 1017 3 MAJ A 5
    1017 1017 3 MAJ R 0
    1017 NATIONAL 3 MAJ A 7592
    1017 NATIONAL 3 MAJ R 863
    1018 1018 0 N/A A 1
    1018 1018 0 N/A R 0
    1018 NATIONAL 0 N/A A 12495
    1018 NATIONAL 0 N/A R 52
    1019 1019 1 MIN A 1
    1019 1019 1 MIN R 0
    1019 NATIONAL 1 MIN A 31218
    1019 NATIONAL 1 MIN R 430
    1020 1020 4 E A 4
    1020 1020 4 E R 0
    1020 NATIONAL 4 E A 1105
    1020 NATIONAL 4 E R 200
    1021 1021 2 MOD A 2
    1021 1021 2 MOD R 0
    1021 NATIONAL 2 MOD A 41296
    1021 NATIONAL 2 MOD R 1909
    1022 1022 1 MIN A 3
    1022 1022 1 MIN R 0
    1022 NATIONAL 1 MIN A 31218
    1022 NATIONAL 1 MIN R 430
    1023 1023 1 MIN A 2
    1023 1023 1 MIN R 0
    1023 NATIONAL 1 MIN A 31218
    1023 NATIONAL 1 MIN R 430
    1024 1024 1 MIN A 1
    1024 1024 1 MIN R 0
    1024 NATIONAL 1 MIN A 31218
    1024 NATIONAL 1 MIN R 430
    1025 1025 2 MOD A 5
    1025 1025 2 MOD R 1
    1025 NATIONAL 2 MOD A 41296
    1025 NATIONAL 2 MOD R 1909
    1026 1026 3 MAJ A 14
    1026 1026 3 MAJ R 1
    1026 NATIONAL 3 MAJ A 7592
    1026 NATIONAL 3 MAJ R 863
    1027 1027 2 MOD A 3
    1027 1027 2 MOD R 0
    1027 NATIONAL 2 MOD A 41296
    1027 NATIONAL 2 MOD R 1909
    1028 1028 3 MAJ A 31
    1028 1028 3 MAJ R 0
    1028 NATIONAL 3 MAJ A 7592
    1028 NATIONAL 3 MAJ R 863
    1029 1029 4 E A 20
    1029 1029 4 E R 0
    1029 NATIONAL 4 E A 1105
    1029 NATIONAL 4 E R 200
    1030 1030 1 MIN A 1
    1030 1030 1 MIN R 0
    1030 NATIONAL 1 MIN A 31218
    1030 NATIONAL 1 MIN R 430

    I have tried to create a sql query to return all the data but I do not want anything returned where the R in the AD field is =0 and not just this field but all fields related to the comparison column. For example the very bottom we see 1030 has ad that is r and = 0. So I do not want anything associated with 1030 returned. 1030 has 4 total rows so in my new table I do a make table on 1030 should not appear. I manually constructed an example of the output IF i were only to use the data above but I do have 20k some odd rows in all so cannot clean this manually.

    COMPARISON WHO SVCD SVDESC ADMIT COUNT
    1013 1013 2 MOD A 2
    1013 1013 2 MOD R 1
    1013 NATIONAL 2 MOD A 41296
    1013 NATIONAL 2 MOD R 1909
    1016 1016 3 MAJ A 9
    1016 1016 3 MAJ R 1
    1016 NATIONAL 3 MAJ A 7592
    1016 NATIONAL 3 MAJ R 863
    1025 1025 2 MOD A 5
    1025 1025 2 MOD R 1
    1025 NATIONAL 2 MOD A 41296
    1025 NATIONAL 2 MOD R 1909
    1026 1026 3 MAJ A 14
    1026 1026 3 MAJ R 1
    1026 NATIONAL 3 MAJ A 7592
    1026 NATIONAL 3 MAJ R 863
  2. #2
  3. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Feb 2005
    Posts
    724
    Rep Power
    291
    Does this accomplish your task?

    Code:
    SELECT * 
    FROM [table_name]
    WHERE 
    	comparison not in 
    		(SELECT comparison 
    		FROM [table_name] 
    		WHERE admit = 'r' 
    		and ct = 0
    		)

IMN logo majestic logo threadwatch logo seochat tools logo