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 455,970 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 3,773 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: Programming Forums

Thread Tools Display Modes

Views: 1075 | Replies: 1 | Solved

•

Join Date: Nov 2007

Posts: 52

Reputation: dotNetDummi is an unknown quantity at this point

Rep Power: 2

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 11:12 pm.

Serene Joey

•

Join Date: Oct 2005

Location: Manchester, UK

Posts: 482

Reputation: pty is on a distinguished road

Rep Power: 4

Solved Threads: 34

pty

Offline

Posting Pro in Training

Re: Help in Join table

Nov 27th, 2007

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:

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
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'
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'