November 21st, 2002, 03:46 PM
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
November 30th, 2002, 12:42 AM
You have no join in the table, eg:
where tblSitterCity.cityID = tblSitter.cityID
December 5th, 2002, 02:39 PM
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?
December 5th, 2002, 04:46 PM
Adding this to the query still doesn't do anything.
December 6th, 2002, 01:15 AM
Doesn't make any sense, what do you get if you try:
SELECT * FROM tblSitterCity cityID = 27;
December 6th, 2002, 01:58 PM
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:
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)
tblCities (all the cities we provide service to)
tblSitterCity (an entry for each sitter and each city they do)
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
December 6th, 2002, 03:33 PM
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:
FROM tblSitters a, tblSitterCity b
WHERE b.cityID = 27
AND b.sitterID = a.sitterID
ORDER BY a.rating DESC