How to get data from 2 different tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to get data from 2 different tables

Author

Topic

shahid09
Starting Member

35 Posts

Posted - 2008-10-13 : 22:13:00

Hi All,

we have two tables Table1 and Table2 and both has primary key NUM_ID . I want to select specific columns by running query which returns 3 rows (means NUM_ID are same in 3 rows).

TABLE1

NUM_ID S_NO COLUMN_1 COLUMN_2 COLUMN_3

1350 2 A B C
1350 1 D E F
1120 1 G H I
1240 1 J K L
1240 2 M N O

TABLE2

NUM_ID S_NO COLUMN_4 COLUMN_5 COLUMN_6

1350 2 P Q R
1350 1 S T U
1120 1 V W X
1240 Y Z AA AB
1240 2 AC AD AE

Here I want to retrive COLUMN_2 FROM TABLE1 AND COLUMN_5 FROM TABLE2
WHERE NUM_ID = 1350 AND ORDERBY S_NO

I have written following code but it is not giving me my desired table
Any idea what is wrong in this code ?

quote:

SELECT A.COLUMN_2, B.COLUMN_5 , A.S_NO
FROM TABLE1 A, TABLE2 B
WHERE (A.NUM_ID = 1350 ) and (B.NUM_ID = 1350) and (A.S_NO = B.S_NO)
ORDERBY S_NO

Expected Table should be

Result

S_NO COLUMN_2 COLUMN_5

1 E T
2 B Q

Thanks,
Shahid

wormz666
Posting Yak Master

110 Posts

Posted - 2008-10-13 : 22:25:50

Select a.column_2,b.column_5,a.s_no from table1 a inner join table2 b
on a.num_id=b.num_id
where a.num_id=1350 order by a.s_no

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-10-13 : 22:35:13

Why there is duplicate in Primary Key(according to you) NUM_ID?

shahid09
Starting Member

35 Posts

Posted - 2008-10-13 : 22:41:57

Yes , NUM_ID is a primary key because 2 rows consist of one iteration for example one claim has two different charge form ( 1 for blood test and second is for x-ray) so claim transaction id is same which is NUM_ID here

shahid09
Starting Member

35 Posts

Posted - 2008-10-13 : 23:54:46

Thanks every one for the reply i have one more question how should i handle S_No here ? Because I want correct data should be populated in the row.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-14 : 01:09:45

quote:
Originally posted by shahid09

Yes , NUM_ID is a primary key because 2 rows consist of one iteration for example one claim has two different charge form ( 1 for blood test and second is for x-ray) so claim transaction id is same which is NUM_ID here

I guess primary key should composite including columns NUM_ID and S_No

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-14 : 01:10:43

quote:
Originally posted by shahid09

Thanks every one for the reply i have one more question how should i handle S_No here ? Because I want correct data should be populated in the row.

didnt get that. Arent you getting correct values from solution provided?

shahid09
Starting Member

35 Posts

Posted - 2008-10-14 : 10:52:45

No, Actually I am expecting two rows with COLUMN_2 and COLUMN_5 values but i am getting 4 rows.

Thanks,
Shahid

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-14 : 11:11:56

think this is what you want

SELECT A.COLUMN_2, B.COLUMN_5 , A.S_NO
FROM TABLE1 A
JOIN  TABLE2 B
ON A.S_NO = B.S_NO
AND A.NUM_ID =B.NUM_ID 
WHERE A.NUM_ID = 1350 
ORDER BY S_NO

shahid09
Starting Member

35 Posts

Posted - 2008-10-14 : 11:21:21

Perfect. Thats what i wanted.

Thanks alot everyone.

Regards,
Shahid

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-14 : 11:28:21

quote:
Originally posted by shahid09

Perfect. Thats what i wanted.

Thanks alot everyone.

Regards,
Shahid

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources