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 |
just.net
Starting Member
24 Posts |
Posted - 2009-04-29 : 08:16:19
|
Hello, I want to do a search in my website, i need to performSearch in different tables, the columns names in the results willbe the same because i am doing merge between the tables(in c# code),How can i do merge (or something like that) in the SP ?(instead of doing 5 or 6 SELECT to the DB in c# code and then merge) |
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2009-04-29 : 08:41:48
|
Use UNION, e.g:select a,b,c from aUNION ALLselect a,b,c from b |
 |
|
just.net
Starting Member
24 Posts |
Posted - 2009-04-29 : 10:03:51
|
thanks, this UNION ALL is great, it's working.but i forgot one thing, i also need to know in theresults which table each results are belong to, for example:ID Name Type42424 Dan Clientss32422 Danda Clients24234 Dana Clients42424 Dans users34234 Dant users97797 Dann users45255 Danb suppliers425808 Danccc suppliers423423 Danqqww suppliersI need this because i need to redirect the user tothe correct page according to the type, so i creared somethingthat i just learned:ALTER PROCEDURE [dbo].[spPlutoSearch] @parameter nvarchar(50) = NULLAS DECLARE @type nvarchar(10)BEGIN SET NOCOUNT ON; CREATE TABLE #Results ( ID int, Name nvarchar(50), Email nvarchar(50) ) SET @type = 'Clients' INSERT INTO #Results(ID, Name, Email) SELECT UserID AS ID, UserName AS Name, Email FROM UsersAuthorizationsView INSERT INTO #Results(ID, Name, Email) SELECT ClientID AS ID, LastFirstPet AS Name, Email FROM SearchResultView INSERT INTO #Results(ID, Name) SELECT PetID AS ID, Name AS Name FROM SearchResultView SELECT * FROM Results WHERE ID LIKE '%' + @parameter + '%' OR Name LIKE '%' + @parameter + '%'ENDNow after this, how can i insert the @type 'Clients' (e.g)INTO the temp table?(i can add the column 'Type' to all of the tables, but it seems not right) |
 |
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2009-04-29 : 10:33:17
|
That is the only way to do it. You can also do this with the Union instead:select a.ID, a.Name, a.Email, a.TabNameFROM (SELECT UserID AS ID, UserName AS Name, Email, 'users' as TabNameFROM UsersAuthorizationsViewUNION ALLSELECT ClientID AS ID, LastFirstPet AS Name, Email, 'clients' as TabNameFROM SearchResultViewUNION ALLSELECT PetID AS ID, Name AS Name, NULL as Email, 'suppliers' as TabNameFROM SearchResultView) aWHERE a.ID LIKE '%' + @parameter + '%' OR a.Name LIKE '%' + @parameter + '%' |
 |
|
just.net
Starting Member
24 Posts |
Posted - 2009-04-29 : 11:36:10
|
i did it. thanks to you!ALTER PROCEDURE [dbo].[spPlutoSearch] @parameter nvarchar(50) = NULLASBEGIN SET NOCOUNT ON; CREATE TABLE #ResultsUsers ( ID int, FullName nvarchar(50), ObjectType nvarchar(10) Default 'Users' ) CREATE TABLE #ResultsClients ( ID int, FullName nvarchar(50), ObjectType nvarchar(10) Default 'Clients' ) CREATE TABLE #ResultsPets ( ID int, FullName nvarchar(50), ObjectType nvarchar(10) Default 'Pets' ) INSERT INTO #ResultsUsers(ID, FullName) SELECT UserID AS ID, FullName FROM UsersAuthorizationsView INSERT INTO #ResultsClients(ID, FullName) SELECT ClientID AS ID, FullName FROM ClientsView INSERT INTO #ResultsPets(ID, FullName) SELECT PetID AS ID, Name FROM PetsView SELECT * FROM(SELECT * FROM #ResultsUsers UNION ALL SELECT * FROM #ResultsClients UNION ALL SELECT * FROM #ResultsPets) AS A WHERE ID LIKE '%' + @parameter + '%' OR FullName LIKE '%' + @parameter + '%'ENDThanks! |
 |
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2009-04-29 : 11:59:17
|
No problem, but you do not need the Inserts into the #temp tables, you are just using extra resource doing it that way.Instead of all the code you have, you could simply do:ALTER PROCEDURE [dbo].[spPlutoSearch]@parameter nvarchar(50) = NULLASBEGINSET NOCOUNT ON;SELECT a.ID, a.FullName, a.ObjectTypeFROM (SELECT UserID AS ID, FullName, 'Users' as ObjectType FROM UsersAuthorizationsViewUNION ALLSELECT ClientID AS ID, FullName, 'Clients' as ObjectType FROM ClientsViewUNION ALLSELECT PetID AS ID, Name AS FullName, 'Pets' as ObjectType FROM PetsView) aWHERE a.ID LIKE '%' + @parameter + '%' OR a.FullName LIKE '%' + @parameter + '%' |
 |
|
just.net
Starting Member
24 Posts |
Posted - 2009-04-29 : 12:31:15
|
I thought your query will not work because of this:'Users' as ObjectType, but i tried and its worked!!i also missed this part: SELECT a.IDnow its much better: SELECT results.ID, results.FullName, results.ObjectType FROM ( SELECT UserID AS ID, FullName, 'Users' as ObjectType FROM UsersAuthorizationsView UNION ALL SELECT ClientID AS ID, FullName, 'Clients' as ObjectType FROM ClientsView UNION ALL SELECT PetID AS ID, Name AS FullName, 'Pets' as ObjectType FROM PetsView) AS results WHERE results.ID LIKE '%' + @parameter + '%' OR results.FullName LIKE '%' + @parameter + '%'.thank you very very much!!! |
 |
|
|
|
|
|
|