#1
  1. queen of livorna
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2002
    Location
    California
    Posts
    8
    Rep Power
    0

    Question 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
  2. #2
  3. Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Oct 2001
    Location
    New Zealand
    Posts
    1,774
    Rep Power
    24
    You have no join in the table, eg:

    where tblSitterCity.cityID = tblSitter.cityID
  4. #3
  5. Web Developer
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2001
    Location
    Pennsylvania
    Posts
    171
    Rep Power
    0
    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?
  6. #4
  7. queen of livorna
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2002
    Location
    California
    Posts
    8
    Rep Power
    0
    Adding this to the query still doesn't do anything.

    Originally posted by binky
    You have no join in the table, eg:

    where tblSitterCity.cityID = tblSitter.cityID
  8. #5
  9. Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Oct 2001
    Location
    New Zealand
    Posts
    1,774
    Rep Power
    24
    Doesn't make any sense, what do you get if you try:

    SELECT * FROM tblSitterCity cityID = 27;
  10. #6
  11. queen of livorna
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2002
    Location
    California
    Posts
    8
    Rep Power
    0
    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
  12. #7
  13. Big Endian
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    May 2001
    Location
    Fly-over country
    Posts
    1,172
    Rep Power
    30
    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

IMN logo majestic logo threadwatch logo seochat tools logo