Retrieving data from Two Temp 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

Retrieving data from Two Temp Tables

Author

Topic

teamjai
Yak Posting Veteran

52 Posts

Posted - 2013-02-05 : 08:44:14

I have the following two tables:

Table 1: #Lo
ID LName
--- ---------
789 L1
963 L2

Table 2: #PD

ID PName
---- -----------
789 P1
789 P2
874 P3

I want to select LName and PName from two tables.
-->where ID = '789'

Result:
ID Name
----- ---------
789 L1
789 P1
789 P2

Thanks

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-05 : 09:03:34

One way to do would be to use a UNION

SELECT
	ID, PName AS NAME 
FROM 
	#PD
WHERE
	ID = '789'
UNION
SELECT
	a.ID,
	a.LName
FROM
	#Lo a
	INNER JOIN #PD b ON
		b.ID = a.ID
WHERE
	b.ID ='789'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-05 : 10:07:12

quote:
Originally posted by James K

One way to do would be to use a UNION
SELECT
ID, PName AS NAME
FROM
#PD
WHERE
ID = '789'
UNION
SELECT
a.ID,
a.LName
FROM
#Lo a
INNER JOIN #PD b ON
b.ID = a.ID
WHERE
b.ID ='789'

what the purpose of the join with first table?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-05 : 10:13:28

quote:
what the purpose of the join with first table?

No need to - left over from how I started the query. Strike that.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-05 : 10:14:39

quote:
Originally posted by James K

quote:
what the purpose of the join with first table?
No need to - left over from how I started the query. Strike that.

Ok..was a bit confused seeing that. Thought I missed something

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

teamjai
Yak Posting Veteran

52 Posts

Posted - 2013-02-06 : 00:13:22

Thanks..

Its work fine. and the query working only Same GUID in the two tables.

Now i need to retrieve the data according to the GUID from any one table

Eaxmple:

where Id= '963'

Result:
ID Name
----- --------
963 L2

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-06 : 00:32:18

same query itself just use new id value


SELECT
	ID, PName AS NAME 
FROM 
	#PD
WHERE
	ID = '963'
UNION
SELECT
	a.ID,
	a.LName
FROM
	#Lo a
WHERE
	a.ID ='963'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

teamjai
Yak Posting Veteran

52 Posts

Posted - 2013-02-06 : 01:33:49

Many Thanks..

i have one more question..

i use this same query but i removed the column "PName" and LName.

SELECT
ID
FROM
#PD
WHERE
ID = '789'
UNION
SELECT
a.ID,

FROM
#Lo a
WHERE
a.ID ='789'

Result comes only one ID,
ID
-------
789

But i need to get 3 IDs,
ID
-------
789
789
789

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-06 : 01:35:27

if you want to retain duplicates use UNION ALL


SELECT
ID 
FROM 
#PD
WHERE
ID = '789'
UNION ALL
SELECT
a.ID

FROM
#Lo a
WHERE
a.ID ='789'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources