Hi! Try this: select t1.id, t1.standartId, t2.id from tasks as t1 left join tasks as t2 using(standartId) where t1.id='jimmy' Saturday, April 24, 2004, 5:12:56 PM, Jimmy wrote: JB> I'm using MS SQL Server 2000 and I have a table called tasks with the JB> following schema: JB> id= int identity 1 1 not null JB> standardId= varchar(15) not null JB> userId= varchar(15) not null JB> status= varchar(15) not null JB> beginDt= datetime JB> endDate=datetime JB> active=char(1) // flag 1=display 0=do not display JB> I need to return records based on userId and standardId. Assume I have the JB> following JB> five records: JB> userId: jimmy standardId: EC.01.10 JB> userId: jimmy standardId: RI.03.20 JB> userId: karen standardId: PC.02.40 JB> userId: sally standardId: EC.01.10 JB> userId: ted standardId: RI.03.20 JB> userId: joe standardId: EC.01.10 JB> These are the results I need to return: JB> All of jimmy's records: where userId='jimmy' JB> I also need to return all records that match all of jimmy's standardId(s), JB> in other words, I need to return in all other records where JB> standardId='EC.01.10 or JB> standardId='RI.03.20 (without duplicating jimmy's results). Which in this JB> scenario would be userId sally, joe, ted JB> I also need to group the records by standardId. JB> Can this be done in a subquery or is two queries required? JB> Having worked mostly with MySQL I dont' have a lot of experience with JB> subqueries. JB> Any suggestions on the best way to approach this probelm? -- Best regards, Mikhail U. Petrov mailto:mikhail@xxxxxxxx -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
