Help in Join table

•

What is DaniWeb IT Discussion Community?

You're currently browsing the MySQL section within the Web Development category of DaniWeb, a massive community of 397,852 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 2,328 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.

Join our community!

Please support our MySQL advertiser:

Thread Tools Display Modes

Views: 930 | Replies: 1 | Solved

Previous Post in Thread

Next Post in Thread

•

Join Date: Nov 2007

Posts: 51

Reputation: dotNetDummi is an unknown quantity at this point

Rep Power: 1

Solved Threads: 0

dotNetDummi dotNetDummi is offline

Offline

Junior Poster in Training

Help in Join table

Nov 22nd, 2007

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

Help with Code Tags

(Toggle Plain Text)

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

Help with Code Tags

(Toggle Plain Text)

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)
);

Last edited by dotNetDummi : Nov 22nd, 2007 at 10:12 pm.

Serene Joey