| Jimmy Brock 2004-04-24, 10:30 am |
| I'm using MS SQL Server 2000 and I have a table called tasks with the
following schema:
id= int identity 1 1 not null
standardId= varchar(15) not null
userId= varchar(15) not null
status= varchar(15) not null
beginDt= datetime
endDate=datetime
active=char(1) // flag 1=display 0=do not display
I need to return records based on userId and standardId. Assume I have the
following
five records:
userId: jimmy standardId: EC.01.10
userId: jimmy standardId: RI.03.20
userId: karen standardId: PC.02.40
userId: sally standardId: EC.01.10
userId: ted standardId: RI.03.20
userId: joe standardId: EC.01.10
These are the results I need to return:
All of jimmy's records: where userId='jimmy'
I also need to return all records that match all of jimmy's standardId(s),
in other words, I need to return in all other records where
standardId='EC.01.10 or
standardId='RI.03.20 (without duplicating jimmy's results). Which in this
scenario would be userId sally, joe, ted
I also need to group the records by standardId.
Can this be done in a subquery or is two queries required?
Having worked mostly with MySQL I dont' have a lot of experience with
subqueries.
Any suggestions on the best way to approach this probelm?
|