November 3, 2005 at 2:39 pm
i have 3 tables which are as follows:
itaps_users
userID int PK
fname
lname
username
password
itaps_rights
id int PK
userID
RAENID
itaps_RAENS
RAENid int PK
title
description
i am trying to write a query to return the users who are a member of a given RAEN. here is the query i am playing with:
SELECT itaps_users.username, itaps_users.id,
(select top 1 itaps_rights.RAENID
from itaps_rights
where itaps_users.id=itaps_rights.userid) as 'RAEN'
FROM itaps_users
WHERE RAEN = 3
if i remove the last line (WHERE RAEN = 3) the query works and returns all users and their RAEN ID. but i only want to return users and their RAEN ID if their RAEN ID is 3.
what am i doing wrong?
November 3, 2005 at 11:54 pm
I don't know why you're using a subquery for this...
SELECT itaps_users.username, itaps_users.id, itaps_rights.RAENID
FROM itaps_users INNER JOIN itaps_rights on itaps_users.id=itaps_rights.userID
WHERE itaps_rights.RAENID=3
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 4, 2005 at 8:27 am
thank you! i had copied and pasted and modified this query from another page where i was using a subquery and my mind was thinking subquery since it was already there. don't know why i didn't think of the inner join.
thanks!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply