query question

[PurpleMonkey]

i'm trying to run this query,

 

select id from tblClientID where city = 'San Francisco' and city = 'San Jose'

 

 

if i have an id that can have several cities, shouldn't it display the id? my query returns nothing.

 

i.e., id 5 can have city san francisco and san jose and id 4 can only have san jose. what i want to do is if san francisco and san jose is chosen, that it will only return id 5. i don't want to use 'OR' because it will return both 4 and 5.

 

what am i doing wrong? if this isn't possible, how do i fix it?

 

i'm using MS Access.

[PlausiblyDamp]

It would only bring back entries where the city field was both San Francisco and San Jose at the same time - you would never get any results back.

[PurpleMonkey]

Is there any way of finding out if, i.e. id 5 belongs to san francisco and san jose? i want to be able to find out if they belong to both, not just one or the other.

[kahlua001]

From your statment. You have a city field in your table. This table conatins 1 city for each client, so what PlausiblyDamp is saying is that is only 1 city, not both. If you want a client to have many cities, then you have to separate out the city field into its own table then join on the client ID.

[PurpleMonkey]

i think i'm having a major brain fart, but could you provide an example?

[kahlua001]

CLIENTS TABLE

ClientID int IDENTITY

CompanyName varchar(100)

 

CLIENT_CITIES TABLE

ClientID int

City varchar(100)

 

SELECT clients.companyname

FROM client_cities INNER JOIN

clients ON client_cities.clientid = clients.clientid INNER JOIN

client_cities client_cities_1 ON clients.clientid = client_cities_1.clientid

WHERE (client_cities.city = 'San Francisco') AND (client_cities_1.city = 'San Jose')

[iebidan]

Will be easier if we know the structure of the table(s) involved, also if there is more than 1 table, the query by kahlua001 is ok too, but can be made in ANSI SQL to have more camptibility with other databases like SQL Server, Oracle, etc.

[kahlua001]

Yea thats true, I just figured it was SQL Server...

[PurpleMonkey]

I'm using MS Access.

 

Here's an example of what i've been working on.

 

cityTable - Table name

cityID as Number (Primary Key)

cityName as Text

 

ClientTable

clientID as Number (Primary Key)

clientName as Text

 

ClientCityTable

clientID as number

ctyID as number

 

 

ClientCityTable can have several of the same clients listed but with different cityIDs.

 

i thought i kept this fairly simple, but perhaps i have it wrong.