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 |
|
russella
Starting Member
3 Posts |
 Posted - 2008-01-03 : 22:11:18
|
| Hi All,How can i query/join the to two tables?see below is my scenario with table1,table2 and results.Table1userid firstname lastnamejames.thomas james thomasjohn.johnson john johnsonTable2userid name valuejames.thomas country australiajohn.johnson title singaporeResults:userid | firstname | lastname| country| titlejames.thomas | james | thomas | australia | managerjohn.johnson | john | johnson | singapore | programmerAny help is very much appreciated!Thanks a lot! |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-01-03 : 22:51:17
|
| Try this:-SELECT t1.userid,t1.firstname,t1.lastname,c.value as 'country',t.value as 'title'FROM Table1 t1CROSS APPLY(SELECT value FROM Table2 where userid=t1.userid AND name='country') cCROSS APPLY(SELECT value FROM Table2 where userid=t1.userid AND name='title') c |
 |
|
|
russella
Starting Member
3 Posts |
Posted - 2008-01-04 : 00:57:59
|
| Hi Visakh16,Thank you so much for your quick help!More power! |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2008-01-04 : 01:37:04
|
this seems simpler to me:select t1.userid, t1.firstname, t1.lastname, t2.value as country, t3.value as titlefrom Table1 t1left join Table2 t2 on t1.userid=t2.userid and t2.name='country'left join Table2 t3 on t1.userid=t3.userid and t3.name='title'
elsasoft.org |
|
|
|
russella
Starting Member
3 Posts |
Posted - 2008-01-04 : 01:49:56
|
| is it possible to make the [Name] dynamic ('country,title')? for example a user wants to display only what he/she selected like country only? is it possible pass a parameters (Selected Items)? |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2008-01-04 : 02:14:14
|
quote:
Originally posted by russella
is it possible to make the [Name] dynamic ('country,title')? for example a user wants to display only what he/she selected like country only? is it possible pass a parameters (Selected Items)?
sure, just pass them in params:select t1.userid, t1.firstname, t1.lastname, t2.value as country, t3.value as titlefrom Table1 t1left join Table2 t2 on t1.userid=t2.userid and t2.name=@name1left join Table2 t3 on t1.userid=t3.userid and t3.name=@name2
elsasoft.org |
|
|
|
|
|
|
|
|
Cross Apply or Join