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

Question

Post reply

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

Jonathan Turlington

Old Hand

Points: 360
More actions
April 18, 2005 at 5:07 pm

#76285

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

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

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

Dinesh Asanka SSChampion Points: 11058 More actions · Answer 1

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/

Meghana SSC Eights! Points: 840 More actions · Answer 2

Your question is not clear.

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

Regards

Meghana

Regards,

Meghana

Jonathan Turlington Old Hand Points: 360 More actions · Answer 3

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

noeld SSC Guru Points: 96590 More actions · Answer 4

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

Jonathan Turlington Old Hand Points: 360 More actions · Answer 5

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.

Vladan SSC-Insane Points: 22072 More actions · Answer 6

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