sql server programming: I've two tables where their composite keys are foreign keys that references each other.

table1 (projId, userId, name, add)
table2 (projId, userId, status, responseDate, reason)

I need to retrieve table1(name,add) and table2( status, responseDate, reason) with projId only..

my query is
Select ap.name, ap.add, p.status,
p.responseDate, p.reason
from table1 ap, table2 p
where p.projID = ap.projID
and ap.userId = p.userId
and p.projectID = 'test1'

but the output is like this:

Name Add Status responseDate Reason
name1 123 Pending 21-11-2007
name1 123 Approved 21-11-2007
name1 123 Rejected 21-11-2007 Invalid name provided


The last three columns are correct but name and add is not.
I need my result to be like

Name Add Status responseDate Reason
name1 123 Pending 21-11-2007
name2 456 Approved 21-11-2007
name3 789 Rejected 21-11-2007 Invalid name provided

What have I do wrong? Please help. Thanks



From http://www.developmentnow.com/g/113_0_0_0_0_0/sql-server-programming.htm

Posted via DevelopmentNow.com Groups