SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Retrieving data from Two Temp Tables		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

teamjai
Starting Member

India
33 Posts
Posted - 02/05/2013 :  08:44:14  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

1500 Posts
Posted - 02/05/2013 :  09:03:34  Show Profile  Reply with Quote
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'
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47069 Posts
Posted - 02/05/2013 :  10:07:12  Show Profile  Reply with Quote
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/
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

1500 Posts
Posted - 02/05/2013 :  10:13:28  Show Profile  Reply with Quote
quote:
what the purpose of the join with first table?
No need to - left over from how I started the query. Strike that.
Edited by - James K on 02/05/2013 10:13:41
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47069 Posts
Posted - 02/05/2013 :  10:14:39  Show Profile  Reply with Quote
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/
Go to Top of Page

teamjai
Starting Member

India
33 Posts
Posted - 02/06/2013 :  00:13:22  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47069 Posts
Posted - 02/06/2013 :  00:32:18  Show Profile  Reply with Quote
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/
Go to Top of Page

teamjai
Starting Member

India
33 Posts
Posted - 02/06/2013 :  01:33:49  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47069 Posts
Posted - 02/06/2013 :  01:35:27  Show Profile  Reply with Quote
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/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000