April 18, 2005 at 5:07 pm
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
April 18, 2005 at 10:05 pm
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:
April 19, 2005 at 2:47 am
April 19, 2005 at 7:52 am
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
April 19, 2005 at 3:07 pm
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
April 19, 2005 at 4:22 pm
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.
April 20, 2005 at 2:26 am
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