query data - SQL Server Forums

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

query data

Author

Topic

nextaxtion
Yak Posting Veteran

54 Posts

Posted - 2014-04-04 : 09:29:46

hi team,

i have following data:

DECLARE @TBL_DTL TABLE (ID INT , MAK_ID INT , AUTH_ID INT )

DECLARE @TBL_USR TABLE(ID INT , NAME VARCHAR(10))

INSERT INTO @TBL_DTL (ID , MAK_ID , AUTH_ID)
VALUES(1 , 100 , 101 )

INSERT INTO @TBL_DTL (ID , MAK_ID , AUTH_ID)
VALUES(2 , 101 , 102 )

INSERT INTO @TBL_DTL (ID , MAK_ID , AUTH_ID)
VALUES(3 , 102 , 103 )

INSERT INTO @TBL_DTL (ID , MAK_ID , AUTH_ID)
VALUES(4 , 101 , 103 )

INSERT @TBL_USR (ID , NAME)
VALUES(100 , 'SAM')

INSERT @TBL_USR (ID , NAME)
VALUES(101 , 'JOHN')

INSERT @TBL_USR (ID , NAME)
VALUES(102 , 'RAJ')

INSERT @TBL_USR (ID , NAME)
VALUES(103 , 'RAJ')

and i need output in following format according to data.

id mak_id maker_name auth_id author_name

prithvi nath pandey

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-04-04 : 16:26:11

Show us what you have so far. This is a very simple query. You need to join the tables together.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

nextaxtion
Yak Posting Veteran

54 Posts

Posted - 2014-04-05 : 00:25:18

I Have write this query

select A.ID ,A.MAK_ID , B.NAME makname , A.AUTH_ID , B.NAME authname
from @TBL_DTL A, @TBL_USR B
WHERE A.MAK_ID = B.ID

but i returnd wrong data for authname field.

Kindly suggest

prithvi nath pandey

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-04-05 : 00:35:16

[code]WHERE ~~A.MAK_ID~~ A.AUTH_ID = B.ID [/code]

KH
[spoiler]Time is always against us[/spoiler]

nextaxtion
Yak Posting Veteran

54 Posts

Posted - 2014-04-05 : 00:43:38

then it will wrong show makname.

prithvi nath pandey

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-04-05 : 00:47:35

or sorry, you want to show both MAK & AUTH name ?
then you will need to INNER JOIN to the @TBL_USR twice


FROM        @TBL_DTL A
INNER JOIN  @TBL_USR B ON A.MAK_ID  = B.ID
INNER JOIN  @TBL_USR C ON A.AUTH_ID = C.ID

KH
[spoiler]Time is always against us[/spoiler]

nextaxtion
Yak Posting Veteran

54 Posts

Posted - 2014-04-05 : 00:58:37

Thanks a lot khtan.

prithvi nath pandey

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources