Post reply

Substitution Data Value

  • December 10, 2004 at 8:20 am

    #60615

    Hi All,

    I have the following query and I want to show column users2.email where the foo1.toid is present...both the foo1.fromid and foo1.toid link back to the users.userid...how do I pull in the email instead of the toid of the user?  The BOLD value represents what I want to substitute when userid = some user then show user's email...

    create table foo1

    (date datetime null,

    fromid int null,

    toid int null,

    body ntext null)

    go

    create table users2

    (userid int PRIMARY KEY Not null,

    email nvarchar (100)null)

    go

    select CAST(foo1.date AS nvarchar(12)) "Date Of Activity", foo1.toid AS "SENT TO USER", foo1.body AS "CONVERSATION DETAILS"

       FROM foo1, users2

       WHERE foo1.fromid = users2.userid and users2.email = 'test.user@hotmail.com' and foo1.date between '9/01/04' and '12/30/04'

       Order by foo1.date

    Thanks...

  • December 10, 2004 at 8:53 am

    #533354

    Can it be you're looking for a CASE expresssion?

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • December 10, 2004 at 9:18 am

    #533361

    select CAST(foo1.date AS nvarchar(12)) "Date Of Activity", users2b.email AS "SENT TO USER",

    foo1.body AS "CONVERSATION DETAILS"

    FROM foo1, users2 as users2a, users2 as users2b

    WHERE foo1.fromid = users2a.userid and foo1.toid = users2b.userid and users2a.email = 'test.user@hotmail.com'

    and foo1.date between '1/01/04' and '12/30/04'

    Order by foo1.date

     

  • December 10, 2004 at 9:31 am

    #533366

    Thanks a bunch all...I used Shaun suggestion and it worked like a charm...

    Again THANKS

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply