SQL statement problem 1

[berkshirea]

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 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

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

step #5 is the final recordset that i want to see as a result.

i hope i have illustrated enough my problem. thanks for any advice/help.



.

 

[bborissov]

[COLOR=green]/* Preparing test table. You don't need this: */[/color]
[COLOR=green]/* In the query just change @Tbl1 to your real table name */[/color]
[COLOR=green]/* whereever you find it */[/color]

[COLOR=blue]DECLARE[/color] @Tbl1 [COLOR=blue]TABLE[/color] (wordtexts  [COLOR=blue]varchar[/color](200), Ip [COLOR=blue]varchar[/color](20))
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Tbl1 [COLOR=blue]VALUES[/color] ([COLOR=red]'tokyo'[/color],[COLOR=red]'1.1.1.1'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Tbl1 [COLOR=blue]VALUES[/color] ([COLOR=red]'singapore'[/color],[COLOR=red]'2.2.2.2'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Tbl1 [COLOR=blue]VALUES[/color] ([COLOR=red]'london'[/color],[COLOR=red]'8.8.8.8'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Tbl1 [COLOR=blue]VALUES[/color] ([COLOR=red]'manila'[/color],[COLOR=red]'3.3.3.3'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Tbl1 [COLOR=blue]VALUES[/color] ([COLOR=red]'paris'[/color],[COLOR=red]'5.5.5.5'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Tbl1 [COLOR=blue]VALUES[/color] ([COLOR=red]'munich'[/color],[COLOR=red]'2.2.2.2'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Tbl1 [COLOR=blue]VALUES[/color] ([COLOR=red]'moscow'[/color],[COLOR=red]'1.1.1.1'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Tbl1 [COLOR=blue]VALUES[/color] ([COLOR=red]'new york'[/color],[COLOR=red]'7.7.7.7'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Tbl1 [COLOR=blue]VALUES[/color] ([COLOR=red]'tokyo'[/color],[COLOR=red]'4.4.4.4'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Tbl1 [COLOR=blue]VALUES[/color] ([COLOR=red]'vancouver'[/color],[COLOR=red]'5.5.5.5'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Tbl1 [COLOR=blue]VALUES[/color] ([COLOR=red]'athens'[/color],[COLOR=red]'6.6.6.6'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Tbl1 [COLOR=blue]VALUES[/color] ([COLOR=red]'mumbai'[/color],[COLOR=red]'1.1.1.1'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Tbl1 [COLOR=blue]VALUES[/color] ([COLOR=red]'hokaido'[/color],[COLOR=red]'2.2.2.2'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Tbl1 [COLOR=blue]VALUES[/color] ([COLOR=red]'java'[/color],[COLOR=red]'5.5.5.5'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Tbl1 [COLOR=blue]VALUES[/color] ([COLOR=red]'tokyo'[/color],[COLOR=red]'2.2.2.2'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Tbl1 [COLOR=blue]VALUES[/color] ([COLOR=red]'tokyo'[/color],[COLOR=red]'8.8.8.8'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Tbl1 [COLOR=blue]VALUES[/color] ([COLOR=red]'manila'[/color],[COLOR=red]'1.1.1.1'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Tbl1 [COLOR=blue]VALUES[/color] ([COLOR=red]'vancouver'[/color],[COLOR=red]'4.4.4.4'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Tbl1 [COLOR=blue]VALUES[/color] ([COLOR=red]'athens'[/color],[COLOR=red]'2.2.2.2'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Tbl1 [COLOR=blue]VALUES[/color] ([COLOR=red]'mumbai'[/color],[COLOR=red]'5.5.5.5'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Tbl1 [COLOR=blue]VALUES[/color] ([COLOR=red]'tokyo'[/color],[COLOR=red]'2.2.2.2'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Tbl1 [COLOR=blue]VALUES[/color] ([COLOR=red]'vancouver'[/color],[COLOR=red]'7.7.7.7'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Tbl1 [COLOR=blue]VALUES[/color] ([COLOR=red]'new york'[/color],[COLOR=red]'2.2.2.2'[/color])
[COLOR=green]/* End of preparing */[/color]

[COLOR=blue]SELECT[/color] [COLOR=#FF00FF]DISTINCT[/color]  Tbl1.* 
[COLOR=blue]FROM[/color] @Tbl1 Tbl1
[COLOR=blue]INNER[/color] [COLOR=blue]JOIN[/color] @Tbl1 Tbl2 [COLOR=blue]ON[/color] Tbl1.Ip = Tbl2.Ip AND Tbl2.wordtexts = [COLOR=red]'tokyo'[/color]
[COLOR=blue]WHERE[/color] Tbl1.Ip <> [COLOR=red]'1.1.1.1'[/color] AND Tbl1.wordtexts <> [COLOR=red]'tokyo'[/color]

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[berkshirea]

Thanks very much Bborissov. That's awesome!!!! Worked like magic!