Post reply

How to get this Result, Unusual Join

  • February 27, 2009 at 4:46 am

    #126736

    Hi All

    I have 2 tables that have, the structure is as given below, i need to change a view so that i can include the table and get a result that i need, there is a problem that i am facing on where clause, i can only use one attribute on a where clause not 2, the structure of the tables and result that are required are as below

    Create Table #Temp

    (

    UserID Varchar(20),

    Name Varchar(50),

    Date DateTime

    )

    Create Table #TempCross

    (

    SUserID Varchar(20),

    UseridRef Varchar(25),

    UserID Varchar(20),

    DateDateTime

    )

    Insert into #Temp Values ('1234','Test123',getdate())

    Insert into #Temp Values ('1235','Test124',getdate())

    Insert into #Temp Values ('1236','Test122',getdate())

    Insert into #Temp Values ('1237','Test121',getdate())

    Insert into #Temp Values ('1238','Test122',getdate())

    Insert into #Temp Values ('S0001248','Test126',getdate())

    Insert into #Temp Values ('S0001230','TestSample',getdate())

    Insert into #TempCross Values ('msudf','1248','1238',getdate())

    Insert into #TempCross Values ('msudf','1230','12337',getdate())

    select a.Date, a.Userid,Name

    from #Temp AS a (nolock) Left Outer Join

    #TempCross as b(nolock) on 'S000'+ cast(b.UseridRef as varchar(20))=a.Userid

    where a.Userid='1238' Or b.Userid='1238'

    The result i get from the Query is as below

    DateUseridName

    27/02/2009 11:39:09.2501238Test122

    27/02/2009 11:39:09.250S0001248Test126

    Here i used a.userid= to get the result, i cant create this as a view, if i can how can i do this, is there any other way to create a view with this 2 table to get this result desired,

    Thanks in advance for the Help 🙂

  • February 27, 2009 at 5:24 am

    #951074

    view are not allowed on tempTables (#xyz) . I think you muss create permanent tables (without #) then it will works

    Sry for my bad english :unsure:

  • February 27, 2009 at 5:27 am

    #951075

    Thanks 321, yeap I am aware of that, this is just a sample ones, if i managed to create a SQL with one Where clause then i can create a view without a where clause.

    U have a good English mate 🙂

  • February 27, 2009 at 6:36 am

    #951112

    Hi Crazyman

    If you look at the result from the sample query

    SELECT a.[Date], a.Userid, a.[Name] , '#', b.UseridRef, b.Userid

    FROM #Temp a (nolock)

    LEFT JOIN #TempCross b(nolock)

    ON 'S000'+ CAST(b.UseridRef AS varchar(20)) = a.Userid

    WHERE a.Userid = '1238' OR

    b.Userid = '1238'

    Date Userid Name UseridRef Userid

    ----------------------- --------- -------- ---------- ------

    2009-02-27 11:59:00.433 1238 Test122 NULL NULL

    2009-02-27 11:59:00.433 S0001248 Test126 1248 1238

    You can see that the two rows have little in common to provide another join pair without screwing up the WHERE clause. I'd use UNION for this, without the ALL qualifier so dupes are eliminated from the result set:

    SELECT [Date], Userid, [Name] FROM (

    SELECT a.[Date], a.Userid, a.[Name], b.Userid AS SearchUserid

    FROM #Temp a (nolock)

    LEFT JOIN #TempCross b(nolock)

    ON 'S000'+ CAST(b.UseridRef AS varchar(20)) = a.Userid

    UNION

    SELECT a.[Date], a.Userid, a.[Name], a.Userid AS SearchUserid

    FROM #Temp a (nolock)

    ) d

    WHERE SearchUserid = '1238'

    Result:

    Date Userid Name

    ----------------------- -------- -------

    2009-02-27 11:59:00.433 1238 Test122

    2009-02-27 11:59:00.433 S0001248 Test126

    Is this what you're looking for?

    Cheers

    ChrisM

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • February 27, 2009 at 8:16 am

    #951197

    Thanks Chris

    This 2 tables are completely different, i just made a sample, in fact there are lots of columns on table #Temp which i need from this matching Query :), its the left outer join, there is also something like performance that has to be taken into consideration since i am having a million rows in a table 🙁 ,

  • February 27, 2009 at 9:05 pm

    #951482

    Make the view without the WHERE clause. Use the WHERE clause when selecting from the view.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 6 posts - 1 through 5 (of 5 total)

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