February 1, 2011 at 9:05 am
hi guys, can you please help me out with this sql stement i am trying to figure out. i've been trying but i think my sql skill is still not that good.
i have a sample table here:
wordtexts ip
-------- ----------
tokyo 1.1.1.1
singapore 2.2.2.2
london 8.8.8.8
manila 3.3.3.3
paris 5.5.5.5
munich 2.2.2.2
moscow 1.1.1.1
new york 7.7.7.7
tokyo 4.4.4.4
vancouver 5.5.5.5
athens 6.6.6.6
mumbai 1.1.1.1
hokaido 2.2.2.2
java 5.5.5.5
tokyo 2.2.2.2
tokyo 8.8.8.8
manila 1.1.1.1
vancouver 4.4.4.4
athens 2.2.2.2
mumbai 5.5.5.5
tokyo 2.2.2.2
vancouver 7.7.7.7
new york 2.2.2.2
and have these steps.
1. exclude rows with ip = 1.1.1.1
wordtexts ip
-------- ----------
singapore 2.2.2.2
london 8.8.8.8
manila 3.3.3.3
paris 5.5.5.5
munich 2.2.2.2
new york 7.7.7.7
tokyo 4.4.4.4
vancouver 5.5.5.5
athens 6.6.6.6
hokaido 2.2.2.2
java 5.5.5.5
tokyo 2.2.2.2
tokyo 8.8.8.8
vancouver 4.4.4.4
athens 2.2.2.2
mumbai 5.5.5.5
tokyo 2.2.2.2
vancouver 7.7.7.7
new york 2.2.2.2
2. note ip's that has tokyo
wordtexts ip
-------- ----------
singapore 2.2.2.2
london 8.8.8.8
manila 3.3.3.3
paris 5.5.5.5
munich 2.2.2.2
new york 7.7.7.7
tokyo 4.4.4.4--------
vancouver 5.5.5.5
athens 6.6.6.6
hokaido 2.2.2.2
java 5.5.5.5
tokyo 2.2.2.2---------
tokyo 8.8.8.8---------
vancouver 4.4.4.4
athens 2.2.2.2
mumbai 5.5.5.5
tokyo 2.2.2.2---------
vancouver 7.7.7.7
new york 2.2.2.2
3. note also other rows of wordtexts that has those ip's
wordtexts ip
-------- ----------
singapore 2.2.2.2--
london 8.8.8.8--
manila 3.3.3.3
paris 5.5.5.5
munich 2.2.2.2--
new york 7.7.7.7
tokyo 4.4.4.4--------
vancouver 5.5.5.5
athens 6.6.6.6
hokaido 2.2.2.2--
java 5.5.5.5
tokyo 2.2.2.2---------
tokyo 8.8.8.8---------
vancouver 4.4.4.4--
athens 2.2.2.2--
mumbai 5.5.5.5
tokyo 2.2.2.2---------
vancouver 7.7.7.7
new york 2.2.2.2--
4. exclude all rows that has tokyo
wordtexts ip
-------- ----------
singapore 2.2.2.2--
london 8.8.8.8--
manila 3.3.3.3
paris 5.5.5.5
munich 2.2.2.2--
new york 7.7.7.7
vancouver 5.5.5.5
athens 6.6.6.6
hokaido 2.2.2.2--
java 5.5.5.5
vancouver 4.4.4.4--
athens 2.2.2.2--
mumbai 5.5.5.5
vancouver 7.7.7.7
new york 2.2.2.2--
5. exclude all rows that has no same marked ip's from #3
and this is the final recordset that i want to see.
wordtexts ip
-------- ----------
singapore 2.2.2.2
london 8.8.8.8
munich 2.2.2.2
hokaido 2.2.2.2
vancouver 4.4.4.4
athens 2.2.2.2
new york 2.2.2.2
i hope i have illustrated enough my problem. thanks for any advice/help.
February 1, 2011 at 9:12 am
The first step is to build a Select statement that will get all the ones you want to exclude directly or indirectly.
select *
from MyTable
where ip = '1.1.1.1'
or wordtexts = 'tokyo';
Then do either a left join or a Where Not In, to that list, and you should have what you need.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 1, 2011 at 10:19 am
GSquared (2/1/2011)
The first step is to build a Select statement that will get all the ones you want to exclude directly or indirectly.
select *from MyTable
where ip = '1.1.1.1'
or wordtexts = 'tokyo';
Then do either a left join or a Where Not In, to that list, and you should have what you need.
Or... just use EXCLUDE.
--Jeff Moden
February 2, 2011 at 6:18 am
Jeff Moden (2/1/2011)
GSquared (2/1/2011)
The first step is to build a Select statement that will get all the ones you want to exclude directly or indirectly.
select *from MyTable
where ip = '1.1.1.1'
or wordtexts = 'tokyo';
Then do either a left join or a Where Not In, to that list, and you should have what you need.
Or... just use EXCLUDE.
Exclude won't get the ones with the same ip as Tokyo. Has to match on all selected columns.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 2, 2011 at 11:56 pm
GSquared (2/2/2011)
Jeff Moden (2/1/2011)
GSquared (2/1/2011)
The first step is to build a Select statement that will get all the ones you want to exclude directly or indirectly.
select *from MyTable
where ip = '1.1.1.1'
or wordtexts = 'tokyo';
Then do either a left join or a Where Not In, to that list, and you should have what you need.
Or... just use EXCLUDE.
Exclude won't get the ones with the same ip as Tokyo. Has to match on all selected columns.
Thanks for the correction, Gus. Not sure what I was thinking there with that OR hanging out as plain as day.
--Jeff Moden
February 3, 2011 at 6:34 am
Jeff Moden (2/2/2011)
Thanks for the correction, Gus. Not sure what I was thinking there with that OR hanging out as plain as day.
It happens.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 10, 2011 at 6:27 am
im sorry GSquared, but can you please show even a simple code of what you mean?
thanks
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply