SQL Newb Needs some help. (If ya''ll can''t help me that''s cool)

  • Here's the deal I need to write a View to where I can do this:

    Select all the data in the table Numbers

    Look and see which rows have 9010 as the value of the CIRC column

    Then look to see of those with 9010 for that value, which have matching columns NPA and NXX to the 9010s.

    Then show all rows except those whose NPA and NXX match a row with the CIRC of 9010, but show the 9010s.

     

     

    Any help would be appreciated.

    I know a little about all this but I just stumped.

     

    Thanks a million in advance.

    Jon

  • Your question is not clear to me. ANyway, check whether follwing is suting your porblem.

    Select * from Numbers Where CIRC = 9010  and NPA <> 9010 and NXX <> 9010




    My Blog: http://dineshasanka.spaces.live.com/

  • Your question is not clear.

    Do you match NPA, NXX from the Numbers table to some other table ?

    Regards

    Meghana

     


    Regards,

    Meghana

  • Ok, here's some sample data. it is all in 1 table.

    NPA   NXX  CIRC

    903   220   0079

    903   221   0008

    903   257   0003

    903   257   9010

    903   269   1076

    903   273   0005

    903   328   0040

    903   328   0053

    903   349   0042

    903   361   0005

    903   361   9010

    903   384   0003

    903   384   9010

    903   386   0003

    903   443   5944

    903   443   9010

    903   448   0004

    903   448   9010

    903   484   9010

    903   551   0005

    903   551   9010

    903   552   0004

    903   552   9010

    903   627   0004

    903   627   9010

    903   642   0004

    903   642   9010

    903   644   2004

    903   698   9010

    903   698   9016

    903   741   9010

    903   751   2160

    903   751   9010

    903   781   0004

    903   781   9010

    903   827   0004

    903   827   9010

    903   920   0056

    903   929   0004

    903   929   9010

    903   964   0004

    903   964   9010

    If two rows have same NPA and NXX, and one of the two matching rows has a CIRC of 9010 then only show the 9010 one, and show all the rest of the rows except the ones eliminated from the view in the first statement. You can see how NPA 903 with NXX 273 is all by itself I want it to show, as is NPA 903 NXX 269, plus any ones with 9010 but eliminate duplicates of the 9010 ones.

    Any help would be greatly appreciated.

    Thanks,

    Jon

  • how about :

    select NPA, NXX, CIRC

    from test

    where CIRC = '9010'

    union all

    select NPA, NXX, CIRC

    from test t2

    where not exists ( select *

         from test t3

                where CIRC = '9010'

        and t2.NPA = t3.NPA and t2.NXX =t3.NXX)

     


    * Noel

  • I get this:

    "View definition includes no output columns or no items in the FROM clause."

    But it definately returns the info I desire, just will not let me save it from enterprise manager.

    Jon

    P.S. I'll try query analyzer.

  • Well, I would probably prefer a self-join over the existence check... EXISTS is rather self-explaining, while here you need comments to make sure other people will quickly understand your code... but after all, that's what comments are for . Try this, if I understood your question correctly, it should work:

    SELECT t.NPA, t.NXX, t.CIRC

    FROM test t

    LEFT JOIN test t2 ON t2.NPA = t.NPA AND t2.NXX = t.NXX AND t2.CIRC = 9010

    WHERE t.CIRC = '9010' OR t2.CIRC IS NULL

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply