need help making this SQL work

I have a table that holds information about sitters. I have another table that has information about which sitters are available for what specific cities. I want to select all sitters who are available for a specific city and then order them by the sitter's ranking.

This is what I'm using, which doesn't give me an error, it just doesn't get any records. I'm sure it's something simple I'm doing wrong, but I can't seem to figure it out.

SELECT * FROM tblSitters, tblSitterCity
WHERE tblSitterCity.cityID = '27'
ORDER BY tblSitters.rating


Thanks,
Sarah

You have no join in the table, eg:

where tblSitterCity.cityID = tblSitter.cityID

Also, I'm fairly new to the SQL game, but say the tblSitterCity.cityID field was an integer value, or numeric, or whatever its called on your platform, would the single quotes still be necessary around 27?

Adding this to the query still doesn't do anything.

Doesn't make any sense, what do you get if you try:

SELECT * FROM tblSitterCity cityID = 27;

This is what I get:

mysql> select * from tblSitterCity where cityID = 27;
+--------------+----------+--------+
| sitterCityID | sitterID | cityID |
+--------------+----------+--------+
| 10024 | 103 | 27 |
| 10025 | 109 | 27 |
| 10026 | 115 | 27 |
| 10027 | 110 | 27 |
| 10028 | 117 | 27 |
| 10029 | 111 | 27 |
| 10030 | 112 | 27 |
+--------------+----------+--------+
7 rows in set (0.00 sec)

Tick marks around the 27 gives the same results.

So the query I'm trying now is:
SELECT *
FROM tblSitters, tblSitterCity
WHERE tblSitterCity.cityID = 27 (27 being only an example)
AND tblSitterCity.cityID = tblSitter.cityID
ORDER BY tblSitters.rating DESC

Perhaps I should give more background on the tables

tblSitters (all of our sitters)
sitterID
sitterName
etc....

tblCities (all the cities we provide service to)
cityID
cityName

tblSitterCity (an entry for each sitter and each city they do)
sitterCityID
sitterID
cityID

I have a cityID and I want to select all the sitters that work in that city. It seems pretty simple.

Thanks again for any help you can provide

Sarah

Your query uses tblSitter.cityID which is a field that does not exist. How are you running this query? If you're running a script, it may be that you're not trapping the error message, not that you're getting zero records.

What does this return:

SELECT *
FROM tblSitters a, tblSitterCity b
WHERE b.cityID = 27
AND b.sitterID = a.sitterID
ORDER BY a.rating DESC