Help me at this query - SQL Server Forums

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.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Help me at this query

Author

Topic

mahdi87_gh
Yak Posting Veteran

72 Posts

Posted - 2012-06-26 : 03:40:41

Hi
I have a Multiple tables.I uploaded my database diagram's picture at here.
[url]http://www.4shared.com/photo/zX4vAK5V/tables.html[/url]

my scenario is : we have users that want to register what works they done.our users categorized in UnitGroups. when one user wanted to register a work,it should determine this information:
1- this work is done for which unit in company? (unitID)
2- what services he did for this work (WorkServices table)
3- which users helped him (CoWorkers table)
4- date, time ,duration , comment

now when i want to show data in a gridview, i used 3 select queries to get information about CoWorkers and Services. how can i get all information just in one query?it need to concat coWorkers's username and services's name.
please help

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-06-26 : 04:42:17

Not a lot to go on there.

Put the query results into temp tables and join them for the result?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

mahdi87_gh
Yak Posting Veteran

72 Posts

Posted - 2012-06-26 : 05:08:42

could u give me some code?
an example?

mahdi87_gh
Yak Posting Veteran

72 Posts

Posted - 2012-06-26 : 05:49:16

I created two Scalar-Value-Function and used them in the query. but still i want a better approach to gain better performance

mahdi87_gh
Yak Posting Veteran

72 Posts

Posted - 2012-06-26 : 23:11:40

when i am doing concatenation ,how to add new line between values?
here is my code

ALTER FUNCTION [dbo].[fn_GetCoWorkers](@workID int)
RETURNS nvarchar(max)
AS
BEGIN
	-- Declare the return variable here
	declare @val nvarchar(max)=''
	
select @val=@val +'\\n' + Rtrim(u.name) from CoWorkers cw 	
INNER Join Users u on cw.userID=u.userID
WHERE cw.workID=@workID

	select @val=stuff(@val,1,1,'');
	RETURN COALESCE(@val,'');

END

mahdi87_gh
Yak Posting Veteran

72 Posts

Posted - 2012-06-27 : 01:45:53

i found it char(13)+char(10)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources