Database Management
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me

The Shed is going Social! Join us on FaceBook and Twitter and chime in on the conversation.

Go Back   Dev Shed ForumsDatabasesDatabase Management

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Dev Shed Forums Sponsor:
  #1  
Old November 21st, 2002, 02:46 PM
disaster77's Avatar
disaster77 disaster77 is offline
queen of livorna
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Nov 2002
Location: California
Posts: 8 disaster77 User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 36 m 56 sec
Reputation Power: 0
Send a message via AIM to disaster77
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

Reply With Quote
  #2  
Old November 29th, 2002, 11:42 PM
binky's Avatar
binky binky is offline
Contributing User
Dev Shed Intermediate (1500 - 1999 posts)
 
Join Date: Oct 2001
Location: New Zealand
Posts: 1,774 binky User rank is Sergeant (500 - 2000 Reputation Level)binky User rank is Sergeant (500 - 2000 Reputation Level)binky User rank is Sergeant (500 - 2000 Reputation Level)binky User rank is Sergeant (500 - 2000 Reputation Level)binky User rank is Sergeant (500 - 2000 Reputation Level) 
Time spent in forums: 1 Day 58 m 15 sec
Reputation Power: 23
You have no join in the table, eg:

where tblSitterCity.cityID = tblSitter.cityID
__________________
Smartfone Warehouse

Reply With Quote
  #3  
Old December 5th, 2002, 01:39 PM
Pasiphae1213's Avatar
Pasiphae1213 Pasiphae1213 is offline
Web Developer
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Oct 2001
Location: Pennsylvania
Posts: 171 Pasiphae1213 User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
Send a message via AIM to Pasiphae1213 Send a message via Yahoo to Pasiphae1213
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?

Reply With Quote
  #4  
Old December 5th, 2002, 03:46 PM
disaster77's Avatar
disaster77 disaster77 is offline
queen of livorna
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Nov 2002
Location: California
Posts: 8 disaster77 User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 36 m 56 sec
Reputation Power: 0
Send a message via AIM to disaster77
Adding this to the query still doesn't do anything.

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

where tblSitterCity.cityID = tblSitter.cityID

Reply With Quote
  #5  
Old December 6th, 2002, 12:15 AM
binky's Avatar
binky binky is offline
Contributing User
Dev Shed Intermediate (1500 - 1999 posts)
 
Join Date: Oct 2001
Location: New Zealand
Posts: 1,774 binky User rank is Sergeant (500 - 2000 Reputation Level)binky User rank is Sergeant (500 - 2000 Reputation Level)binky User rank is Sergeant (500 - 2000 Reputation Level)binky User rank is Sergeant (500 - 2000 Reputation Level)binky User rank is Sergeant (500 - 2000 Reputation Level) 
Time spent in forums: 1 Day 58 m 15 sec
Reputation Power: 23
Doesn't make any sense, what do you get if you try:

SELECT * FROM tblSitterCity cityID = 27;

Reply With Quote
  #6  
Old December 6th, 2002, 12:58 PM
disaster77's Avatar
disaster77 disaster77 is offline
queen of livorna
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Nov 2002
Location: California
Posts: 8 disaster77 User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 36 m 56 sec
Reputation Power: 0
Send a message via AIM to disaster77
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

Reply With Quote
  #7  
Old December 6th, 2002, 02:33 PM
dcaillouet's Avatar
dcaillouet dcaillouet is offline
Big Endian
Dev Shed Beginner (1000 - 1499 posts)
 
Join Date: May 2001
Location: Fly-over country
Posts: 1,172 dcaillouet User rank is Sergeant (500 - 2000 Reputation Level)dcaillouet User rank is Sergeant (500 - 2000 Reputation Level)dcaillouet User rank is Sergeant (500 - 2000 Reputation Level)dcaillouet User rank is Sergeant (500 - 2000 Reputation Level)dcaillouet User rank is Sergeant (500 - 2000 Reputation Level) 
Time spent in forums: 16 h 29 m 5 sec
Reputation Power: 29
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

Reply With Quote
Reply

Viewing: Dev Shed ForumsDatabasesDatabase Management > need help making this SQL work

Developer Shed Advertisers and Affiliates



Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump

Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 


Powered by: vBulletin Version 3.0.5
Copyright ©2000 - 2013, Jelsoft Enterprises Ltd.

© 2003-2013 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap