| Author |
 Topic  |
|
|
kirank
Starting Member
USA
45 Posts |
 Posted - 02/05/2013 : 13:30:06
|
hi,
i know this will be a simple question to you but not able to find proper example.
i want to write a query some thing like this
select *, name=(select name from xyvtable where name =12)
from abctable
here in name=(select name from xyvtable where name =12) i will pass the parameter and base on that name value get populated along with all the records from the abctable and name from xyvtable
Thank You.
---------------------------
http://codingstuffsbykiran.blogspot.com | http://webdevlopementhelp.blogspot.com |
|
|
James K
Flowing Fount of Yak Knowledge
1490 Posts |
Posted - 02/05/2013 : 14:16:30
|
You can try this code:SELECT
a.*,
b.name
FROM
abctable a
LEFT JOIN xyvtable b ON
a.name = b.name AND b.name = '12'
The behavior will change depending on whether the relations ship is one to many or many to one. Also, you can change the LEFT JOIN to INNER JOIN and take a look to see if that is what you want. |
 |
|
|
kirank
Starting Member
USA
45 Posts |
|
|
DonAtWork
Flowing Fount of Yak Knowledge
2111 Posts |
|
|
kirank
Starting Member
USA
45 Posts |
|
|
James K
Flowing Fount of Yak Knowledge
1490 Posts |
Posted - 02/07/2013 : 16:13:32
|
quote:
Originally posted by kirank
hi, its resolved chk this
select age ,isnull((select name from xyc where abc=32),'') as name
from nnn
---------------------------
http://codingstuffsbykiran.blogspot.com | http://webdevlopementhelp.blogspot.com
Just couple of caveats about this:
1. If there is more than one row for abc=32, the query will fail (with a message about subquery returning more than one row).
2. If nnn has more than one row, it is going to return the same value for the name column. |
|
|
|
kirank
Starting Member
USA
45 Posts |
Posted - 02/08/2013 : 14:00:36
|
Hi, thanx for your comments.
i have verify that single records are there in case of (select name from xyvtable where name =12 ) so it will not get break as u suggest.
2. If nnn has more than one row, it is going to return the same value for the name column.
i am not able to get this senario and you give me some example.
---------------------------
http://codingstuffsbykiran.blogspot.com | http://webdevlopementhelp.blogspot.com |
|
|
|
James K
Flowing Fount of Yak Knowledge
1490 Posts |
Posted - 02/08/2013 : 14:33:17
|
Regarding #2, if you copy and paste the code below to a query window and run it, you will see what I meant. There are 3 rows in #nnn, with different values for age, and they all return Smith as the name.CREATE TABLE #nnn(age INT);
CREATE TABLE #xyc(name VARCHAR(64), abc INT);
INSERT INTO #nnn VALUES (10),(20),(30);
INSERT INTO #xyc VALUES ('Smith',32),('Jones',30);
select age ,isnull((select name from #xyc where abc=32),'') as name
from #nnn
DROP TABLE #xyc,#nnn; |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47040 Posts |
Posted - 02/08/2013 : 23:28:53
|
The scenario James suggested will happen so long as you dont have a relation betwwen two tables nnn and xyc
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
query with in query
