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
table2 is the parent table.


This is my create statements

create table table1 (
projectid varchar(10),
userid int identity(1,1),
name varchar(50),
add varchar(20),
email varchar(80),
primary key (projectid, apid)
);

create table table2(
projectid varchar(10),
userid int,
status varchar(15),
reason varchar(100),
responseDate varchar(15),
primary key (projectid,userid),
constraint fk_table1 foreign key (projectid, apid) references table2(projectid, apid)
);

I find it easier to specify the join as follows and seperate the where clause; if your data is correct the following query should work:

select 
    ap.name
   ,ap.add 
   ,p.status
   ,p.responseDate
   ,p.reason
from 
   table1 ap
join 
   table2 p on  p.projID = ap.projID 
   and ap.userId = p.userId
where 
   p.projectID = 'test1'
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.