Prev: generate summary row question
Next: SQL 2008 intellisense errors Invalid Object
From: Erland Sommarskog on 14 Nov 2009 17:09
MS Forum Newgroup User (euser(a)microsoft.com) writes:
> Apologies for the cross post, I was not sure about where to post my query.
>
> You are correct about a join, but I could not get this working hence I
> tried to split this up into 2.
>
> As in the example:
> tbl_main:
> ID, ColumnID1, ColumnID2, ColumnID3, ColumnID4
> 1, AA, AB, AC,
> AD
> 2, AE, AF, AG,
> AH
>
> tbl_login:
> designation, uername, useremail
> AA, username1, useremail1
> AB, username2, useremail2
> AC, username3, useremail3
> AD, username4, useremail4
>
> Just to be clear, if I send @ID int = 1 and @IDName = ColumnID1
>
> the @tempval will return AA
> and the @sql will return username = username1 and useremail = useremail1
> because of the @tempval - AA match

Use a CASE expression:

Select l.username AS UName, isnull(l.useremail,'NA') AS UEmail
from tbl_login l
join tbl_main m ON l.designation =
CASE @IDName
WHEN 'ColumnID1' THEN ColumnID1
WHEN 'ColumnID2' THEN ColumnID2
WHEN 'ColumnID3' THEN ColumnID3
WHEN 'ColumnID4' THEN ColumnID4
END
where m.id= @ID

But if you feel compelled to send in a column name, this indicates
that there is a flaw in the data model.


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: MS Forum Newgroup User on 14 Nov 2009 17:31
Erland,

That work's great!




"Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message
news:Xns9CC3EB86A6332Yazorman(a)127.0.0.1...
> MS Forum Newgroup User (euser(a)microsoft.com) writes:
>> Apologies for the cross post, I was not sure about where to post my
>> query.
>>
>> You are correct about a join, but I could not get this working hence I
>> tried to split this up into 2.
>>
>> As in the example:
>> tbl_main:
>> ID, ColumnID1, ColumnID2, ColumnID3, ColumnID4
>> 1, AA, AB, AC,
>> AD
>> 2, AE, AF, AG,
>> AH
>>
>> tbl_login:
>> designation, uername, useremail
>> AA, username1, useremail1
>> AB, username2, useremail2
>> AC, username3, useremail3
>> AD, username4, useremail4
>>
>> Just to be clear, if I send @ID int = 1 and @IDName = ColumnID1
>>
>> the @tempval will return AA
>> and the @sql will return username = username1 and useremail = useremail1
>> because of the @tempval - AA match
>
> Use a CASE expression:
>
> Select l.username AS UName, isnull(l.useremail,'NA') AS UEmail
> from tbl_login l
> join tbl_main m ON l.designation =
> CASE @IDName
> WHEN 'ColumnID1' THEN ColumnID1
> WHEN 'ColumnID2' THEN ColumnID2
> WHEN 'ColumnID3' THEN ColumnID3
> WHEN 'ColumnID4' THEN ColumnID4
> END
> where m.id= @ID
>
> But if you feel compelled to send in a column name, this indicates
> that there is a flaw in the data model.
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se
>
> Links for SQL Server Books Online:
> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> SQL 2000:
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>
First  |  Prev  | 
Pages: 1 2
Prev: generate summary row question
Next: SQL 2008 intellisense errors Invalid Object