sql query needed to extract data from 4 tables

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 2005 Forums

Transact-SQL (2005)

sql query needed to extract data from 4 tables

Author

Topic

pathania_garima
Starting Member

1 Post

Posted - 2011-07-04 : 09:12:17

I have 4 tables
Sample data and tables are below

SR
ticketid description ticketuid
1012 abcd 4567
1013 efgh 5678
1014 sdfsdf 8901
1015 asdafg 3456

WorkOrder
wonum description origrecordclass origrecordid
12890 tryer SR 1012
12892 fdsfdsg SR 1013
12896 fdsfr SR 1012
12897 gghh WO 12678
12900 fdsfr SR 1014

Worklog
worklogid recordkey description type
1 1012 tkykyy initial
2 1012 asaddewws update
3 12890 mkkhj initial
4 12890 lliu final
5 1013 yyu final
6 1013 yppp initial
7 12900 gskjdfk final

Longdescription
ldkey ldownertable description
4567 SR this is long description of SR 1012
5678 SR this is long description of SR 1013
8901 SR this is long description of SR 1014
3456 SR this is long description of SR 1015
1 Worklog here it is from worlog for recordkey 1012
2 Worklog more for recordkey 1012
3 Worklog here it is from worlog for recordkey 12890
4 Worklog more from worlog for recordkey 12890
5 Worklog here it is from worlog for recordkey 1013
6 Worklog more from worlog for recordkey 1013
7 Worklog here it is from worlog for recordkey 12900

I need a query to display me the below result:

ticketid wonum description worklogdetails
1012 12890 abcd. this is long description of SR 1012 tkykyy. here it is from worklog for recordkey 1012. asaddewws. more for recordkey 1012. mkkhj.here it is from worlog for recordkey 12890.lliu.more from worlog for recordkey 12890
12896

1013 12892 efgh. this is long description of SR 1013 yyu. here it is from worklog for recordkey 1013. yppp. more from worlog for recordkey 1013

sonjan
Starting Member

22 Posts

Posted - 2011-07-05 : 19:45:39

Try something like this:
select distinct
sr.ticketid,
sr.description,
workorder.wonum,
workorder.description,
worklog.*
from
sr with (nolock)
left outer join
workorder with (nolock) on workorder.origrecordclass = sr.origrecordclass and workorder.origrecordid = sr.origrecordid
left outer join
worklog with (nolock) on worklog.siteid = sr.siteid
where
sr.siteid = ' '
and
sr.ticketid = ' '

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources