How to query two tables having composite primary key

Question

Post reply

How to query two tables having composite primary key

ragunaathan

Newbie

Points: 3
More actions
November 6, 2009 at 3:57 am

#213428

CREATE TABLE T1
(idno varchar(30) NOT NULL,
seqno varchar(30) NOT NULL,
Last_Name varchar(30),
First_Name varchar(30)
constraint t1c PRIMARY KEY (idno,seqno)
);
CREATE TABLE T2
(idno varchar(30) NOT NULL ,
seqno varchar(30) NOT NULL ,
FLAG varchar(1),
constraint t2c PRIMARY KEY (idno,seqno)
);
T1
idnoseqnoL_nameF_name
1Aaaaaaaaa
1Bbbbbbbbb
2Aabaababa
2Bbabbabab
3Ccccccccc
4Ddddddddd
5Eeeeeeeee
T2
idnoseqnoFlag
1A0
1B1
2B1
4D0
Result
idnoseqnoL_nameF_name
2Aabaababa
3Ccccccccc
5Eeeeeeeee
How to get the above result, help us out?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 1

can you show us how you have attempted to solve this yourself ?

Clear Sky SQL
My Blog[/url]

ragunaathan Newbie Points: 3 More actions · Answer 2

ragunaathan

Newbie

Points: 3

November 6, 2009 at 5:01 am

#1075828

---

ragunaathan Newbie Points: 3 More actions · Answer 3

select T1.seqno from T1 where T1.seqno not in(select T2.seqno from T2)

this query was written considering only one primary key i.e. seqno.

But how to use both the composite key i.e. idno, seqno in a query to get the required result.

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 4

Hi

Try this:

DECLARE @T1 TABLE

(idno varchar(30) NOT NULL,

seqno varchar(30) NOT NULL,

Last_Name varchar(30),

First_Name varchar(30)

PRIMARY KEY (idno,seqno)

);

DECLARE @T2 TABLE

(idno varchar(30) NOT NULL ,

seqno varchar(30) NOT NULL ,

FLAG varchar(1),

PRIMARY KEY (idno,seqno)

);

INSERT INTO @T1

SELECT '1', 'A', 'aaa', 'aaaaa'

UNION ALL SELECT '1', 'B', 'bbb', 'bbbbb'

UNION ALL SELECT '2', 'A', 'aba', 'ababa'

UNION ALL SELECT '2', 'B', 'bab', 'babab'

UNION ALL SELECT '3', 'C', 'ccc', 'ccccc'

UNION ALL SELECT '4', 'D', 'ddd', 'ddddd'

UNION ALL SELECT '5', 'E', 'eee', 'eeeee';

INSERT INTO @T2

SELECT '1', 'A', '0'

UNION ALL SELECT '1', 'B', '1'

UNION ALL SELECT '2', 'B', '1'

UNION ALL SELECT '4', 'D', '0';

SELECT

t1.*

FROM @T1 t1

LEFT JOIN @T2 t2 ON t1.idno = t2.idno AND t1.seqno = t2.seqno

WHERE t2.idno IS NULL;

Greets

Flo

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 5

As Flo , suggests, which i personally prefer as an exclusion routine

Or you may want check out the except operator as well

Clear Sky SQL
My Blog[/url]