February 27, 2009 at 4:46 am
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
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
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
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
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
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
Make the view without the WHERE clause. Use the WHERE clause when selecting from the view.
--Jeff Moden
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply