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.
| Author |
Topic |
|
dineshrajan_it
Posting Yak Master
217 Posts |
 Posted - 2009-05-12 : 02:05:14
|
| Hi All, I have a Login table which has Loginid,username,pwd,lastmodifiedby as intso i need to get a query to know who modified which usernameSELECT USERNAME,[ModifiedBy]=(SELECT L1.USERNAME FROM LOGIN L1 WHERE L1.LOGINID=L.LOGINID)FROMLOGIN LThis is query. i have used subquery. may be we can use joins. so best solution is to use self-joins for this. why do i have to use same table twice to get desired results.Any other way outthanks in advanceIam a slow walker but i never walk back |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2009-05-12 : 02:24:51
|
[code]SELECT L.USERNAME, MODIFIEDBY = M.USERNAMEFROM LOGIN L INNER JOIN LOGIN M ON L.LASTMODIFIEDBY = M.LOGINID[/code]
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
dineshrajan_it
Posting Yak Master
217 Posts |
Posted - 2009-05-12 : 02:46:19
|
| Thanks for Response,I know we can use self-joins. i thought of is it possible in CTE.Iam a slow walker but i never walk back |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2009-05-12 : 02:47:43
|
the data you need are from 2 separate row. CTE or not, you still have to read 2 rows to get the required data
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
dineshrajan_it
Posting Yak Master
217 Posts |
Posted - 2009-05-12 : 02:51:01
|
quote:
Originally posted by khtan
the data you need are from 2 separate row. CTE or not, you still have to read 2 rows to get the required data
KH[spoiler]Time is always against us[/spoiler]
ThanksIam a slow walker but i never walk back |
|
|
|
|
|
|
|
|
Prob with Select Query