Author |
Topic |
kirank
Yak Posting Veteran
58 Posts |
Posted - 2013-02-05 : 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 thisselect *, name=(select name from xyvtable where name =12)from abctablehere 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 xyvtableThank You.---------------------------http://codingstuffsbykiran.blogspot.com | http://webdevlopementhelp.blogspot.com |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-05 : 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
Yak Posting Veteran
58 Posts |
Posted - 2013-02-07 : 11:52:55
|
Hi, thanx for the response.actually i dont want to use any joins simple querySELECT name,age=(select age from abc where name='xyz') FROM xyzlike this.is that possible.---------------------------http://codingstuffsbykiran.blogspot.com | http://webdevlopementhelp.blogspot.com |
|
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
Posted - 2013-02-07 : 11:58:32
|
Why would you not want to do any joins? The point of a query is to return data. Since the data resides in 2 different tables, the only way to return that data is with a join.How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx |
|
|
kirank
Yak Posting Veteran
58 Posts |
Posted - 2013-02-07 : 13:00:35
|
hi, its resolved chk thisselect age ,isnull((select name from xyc where abc=32),'') as namefrom nnn---------------------------http://codingstuffsbykiran.blogspot.com | http://webdevlopementhelp.blogspot.com |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-07 : 16:13:32
|
quote: Originally posted by kirank hi, its resolved chk thisselect age ,isnull((select name from xyc where abc=32),'') as namefrom 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
Yak Posting Veteran
58 Posts |
Posted - 2013-02-08 : 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
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-08 : 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 namefrom #nnnDROP TABLE #xyc,#nnn; |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-08 : 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 MVPhttp://visakhm.blogspot.com/ |
|
|
|