Skip to main content
W
wfu_king
Known Participant
March 1, 2008
Answered

subquery to get results on either side of query result

  • March 1, 2008
  • 5 replies
  • 1570 views
I need to get room numbers that are 2 above and 2 below the authenticated user's room number. I pretty much knew the attached code wouldn't work, but it's the idea of what I want to do. The top part of the query gives me the authenticated person's room number, then I need to grab all room numbers that are between 2 on either side of him/her. In other words, if user is in room 110, I need all folks in room 108, 109, 111, 112 (and disclude 110). I'm just not skilled enough yet to get this query correct. It is MySQL if that makes a difference.
This topic has been closed for replies.
Correct answer Dan_Bracuk
select username,fname,lname,dorm,room
from psr_subjects p1 join psr_subjects p2 on p1.dorm = p2.dorm

where p1.username = something
and p2.room <> p1.room
and p2.room between p1.room + 2 and p1.room - 2

5 replies

D
Dan_Bracuk
Inspiring
March 3, 2008
On what field are you joining p1 to p2?
W
wfu_kingAuthor
Known Participant
March 4, 2008
I've tried joining on 'dorm', but also tried 'username', which is the primary key.
D
Dan_Bracuk
Inspiring
March 4, 2008
quote:
Originally posted by: rking1966
I've tried joining on 'dorm', but also tried 'username', which is the primary key.

That would explain why you are not getting any data. For p2, you don't want the user's room.
D
Dan_Bracuk
Inspiring
March 3, 2008
If you are not getting data when you think you should, take away all the contstraints and run your query. Then add the constraints back one at a time until you see which one creates the problem.
W
wfu_kingAuthor
Known Participant
March 3, 2008
Yes, I'm doing that and I get data (the one row with the specified username) when I take off both of the last AND's. Adding back either of these AND's by themselves gives me null results.
N
Newsgroup_User
Inspiring
March 2, 2008
Dan Bracuk wrote:
> What I think Azadi meant was not,
> columnname.username
> but
> tableAlias.username

exactly. thanks, Dan.
another proof that one should never attempt to post replies before his
first cup of coffee... :)

Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/
D
Dan_Bracuk
Inspiring
March 2, 2008
What I think Azadi meant was not,
columnname.username
but
tableAlias.username

Too bad you changed that part when you copied my example.
D
Dan_BracukCorrect answer
Inspiring
March 1, 2008
select username,fname,lname,dorm,room
from psr_subjects p1 join psr_subjects p2 on p1.dorm = p2.dorm

where p1.username = something
and p2.room <> p1.room
and p2.room between p1.room + 2 and p1.room - 2
W
wfu_kingAuthor
Known Participant
March 1, 2008
That looks good. I've attached the code I have now (really your's copied) and now I'm getting an error from CF saying " Column 'username' in field list is ambiguous" from the WHERE line. Not sure what they mean...it's the correct field name, etc. #SESSION.psr.username# is a single value, but that should be fine, right?

Thanks!
W
wfu_kingAuthor
Known Participant
March 1, 2008
Actually, the msg is coming from MySQL but I still don't know why. I'm doing the query now in MySQL (using a "username") and still getting the error. Does it have something to do with username being my primary key in the table?
Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices