Select in query sql server

[fran7]

Hi, I wonder if anyone has any pointers to this.

I have a page querying cities from my database.

fields to query in database

country
australia
uk
uk
france
uk

city

sydney
london
brighton
paris
oxford

?Keyword=London

That works fine and the page is populated with all the records in london.

In this page I want to make an additional query with a list of all the other cities in the uk, for example. Thats where I come unstuck.

The query i am working on just gets me the city of the main query and not cities shared by the county of the main query. I have tried select in() and imagine it needs that but where I am lost.

description=Request("keyword")
If description <> "" Then
                               Set rs8=Server.CreateObject("ADODB.Recordset")  
                                rs8.CursorLocation = 3
''" & description & "'
                     '    
SQLQuery = "select table.city from table where table.city=  '" & description & "'  group by city HAVING COUNT(city) > 0 order by count(city) desc"

I hope that make sense

Thanks in advance
Richard

[Luuk]

SELECT .....
WHERE country = 'uk'
  AND NOT city = 'london'
 

[fran7]

Hi, Thanks for the reply. WHERE country = 'uk' is right but i need to collect that info dynamically.

As the query of the page is for city writing WHERE country = '" & description & "' just gets me the same city as the main query and not all the other cities that share the same country.

Thanks for your help
Richard

[Luuk]

OK, but can rewrite what i gave you? or you the answer be spelled out?

What info is missing?

If you want a query with just one keyword (i.e. 'london') and than find all stuff in the 'uk', than i want to know if you have a table which ties 'london' to the 'uk'..

just to be sure it's not this london

[jforbes]

You could try something like:

SQLQuery = "select table.* from table 
LEFT JOIN WhereTable ON table.country=WhereTable.country
where WhereTable.city=  '" & description & "' ..."