Query Help - Joins - 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)

Query Help - Joins

Author

Topic

emmim44
Yak Posting Veteran

65 Posts

Posted - 2012-07-12 : 08:19:26

Hi there, I need to know how would output p.lastname,p.firstname,u.xproxy to below statement... The relation between unsaccountB tbl and person is uid_person AND between unsaccountB tbl and DialogWatchOperation
is ObjectKey. Your help is appreciated...



****** Script for SelectTopNRows command from SSMS  ******/
SELECT top 1 dw.[UID_DialogWatchProperty]
      ,dw.[UID_DialogWatchOperation]
      ,dw.[UID_DialogColumn]
      ,dw.[OldValue]
      ,dwo.operationDate
      ,dwo.DisplayValue
      ,dwo.OperationType
      ,dwo.ObjectKey
FROM [INGIDMTST].[dbo].[DialogWatchProperty] dw,[INGIDMTST].[dbo].[DialogWatchOperation] dwo,Person p, UNSAccountB u
  where dw.UID_DialogColumn = N'159659b1-6911-4516-a2ec-f68890de3fec' 
  and dw.UID_DialogWatchOperation = dwo.UID_DialogWatchOperation
  And OperationType = 'U'
  And  dwo.ObjectKey = ( select XObjectKey from UNSAccountB where UID_Person = (select UID_Person from Person where PersonnelNumber='50256') )
 
  order by dwo.operationDate desc

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2012-07-12 : 08:41:40

SELECT top 1
p.lastname, p.firstname, u.xproxy, dw.[UID_DialogWatchProperty],
dw.[UID_DialogWatchOperation], dw.[UID_DialogColumn], dw.[OldValue],
dwo.operationDate, dwo.DisplayValue, dwo.OperationType, dwo.ObjectKey
FROM [INGIDMTST].[dbo].[DialogWatchProperty] dw
INNER JOIN [INGIDMTST].[dbo].[DialogWatchOperation] dwo ON dw.UID_DialogWatchOperation = dwo.UID_DialogWatchOperation
INNER JOIN UNSAccountB u ON dwo.ObjectKey = u.ObjectKey
INNER JOIN Person p ON p.uid_person = u.uid_person
where dw.UID_DialogColumn = N'159659b1-6911-4516-a2ec-f68890de3fec'
And OperationType = 'U'
And dwo.ObjectKey = ( select XObjectKey
from UNSAccountB
where UID_Person = ( select UID_Person
from Person
where PersonnelNumber = '50256' ) )
order by dwo.operationDate desc

--------------------------
http://connectsql.blogspot.com/

emmim44
Yak Posting Veteran

65 Posts

Posted - 2012-07-12 : 08:50:35

quote:

Originally posted by emmim44

Hi there, I need to know how would output p.lastname,p.firstname,u.xproxy to below statement... The relation between unsaccountB tbl and person is uid_person AND between unsaccountB tbl and DialogWatchOperation
is ObjectKey. Your help is appreciated...



****** Script for SelectTopNRows command from SSMS  ******/
SELECT top 1 dw.[UID_DialogWatchProperty]
      ,dw.[UID_DialogWatchOperation]
      ,dw.[UID_DialogColumn]
      ,dw.[OldValue]
      ,dwo.operationDate
      ,dwo.DisplayValue
      ,dwo.OperationType
      ,dwo.ObjectKey
FROM [INGIDMTST].[dbo].[DialogWatchProperty] dw,[INGIDMTST].[dbo].[DialogWatchOperation] dwo,Person p, UNSAccountB u
  where dw.UID_DialogColumn = N'159659b1-6911-4516-a2ec-f68890de3fec' 
  and dw.UID_DialogWatchOperation = dwo.UID_DialogWatchOperation
  And OperationType = 'U'
  And  dwo.ObjectKey = ( select XObjectKey from UNSAccountB where UID_Person = (select UID_Person from Person where PersonnelNumber='50256') )
 
  order by dwo.operationDate desc

Please ignore this thread.

emmim44
Yak Posting Veteran

65 Posts

Posted - 2012-07-12 : 08:54:13

quote:
Originally posted by lionofdezert

SELECT top 1
p.lastname, p.firstname, u.xproxy, dw.[UID_DialogWatchProperty],
dw.[UID_DialogWatchOperation], dw.[UID_DialogColumn], dw.[OldValue],
dwo.operationDate, dwo.DisplayValue, dwo.OperationType, dwo.ObjectKey
FROM [INGIDMTST].[dbo].[DialogWatchProperty] dw
INNER JOIN [INGIDMTST].[dbo].[DialogWatchOperation] dwo ON dw.UID_DialogWatchOperation = dwo.UID_DialogWatchOperation
INNER JOIN UNSAccountB u ON dwo.ObjectKey = u.ObjectKey
INNER JOIN Person p ON p.uid_person = u.uid_person
where dw.UID_DialogColumn = N'159659b1-6911-4516-a2ec-f68890de3fec'
And OperationType = 'U'
And dwo.ObjectKey = ( select XObjectKey
from UNSAccountB
where UID_Person = ( select UID_Person
from Person
where PersonnelNumber = '50256' ) )
order by dwo.operationDate desc

--------------------------
http://connectsql.blogspot.com/

thank you very much Lion,I also figured it out before or after you posted... :D

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2012-07-12 : 09:05:08

My pleasure

--------------------------
http://connectsql.blogspot.com/

emmim44
Yak Posting Veteran

65 Posts

Posted - 2012-07-12 : 09:07:36

quote:
Originally posted by lionofdezert

SELECT top 1
p.lastname, p.firstname, u.xproxy, dw.[UID_DialogWatchProperty],
dw.[UID_DialogWatchOperation], dw.[UID_DialogColumn], dw.[OldValue],
dwo.operationDate, dwo.DisplayValue, dwo.OperationType, dwo.ObjectKey
FROM [INGIDMTST].[dbo].[DialogWatchProperty] dw
INNER JOIN [INGIDMTST].[dbo].[DialogWatchOperation] dwo ON dw.UID_DialogWatchOperation = dwo.UID_DialogWatchOperation
INNER JOIN UNSAccountB u ON dwo.ObjectKey = u.ObjectKey
INNER JOIN Person p ON p.uid_person = u.uid_person
where dw.UID_DialogColumn = N'159659b1-6911-4516-a2ec-f68890de3fec'
And OperationType = 'U'
And dwo.ObjectKey = ( select XObjectKey
from UNSAccountB
where UID_Person = ( select UID_Person
from Person
where PersonnelNumber = '50256' ) )
order by dwo.operationDate desc

How about if I want add another tbl on the scene. "dw.UID_DialogColumn = N'159659b1-6911-4516-a2ec-f68890de3fec'
" instead of this I want to get only one latest record from DialogWatchOperation using dialogColumn tbl that has a relation with "DialogWatchProperty" on "uid_DialogWatchProperty". I need one lastest record per UID_DialogColumn....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-12 : 09:58:01

[code]
SELECT *
FROM
(
SELECT ROW_NUMBER() OVER(PARTITION BY dw.UID_DialogColumn order by dwo.operationDate desc) AS Seq,
p.lastname, p.firstname, u.xproxy, dw.[UID_DialogWatchProperty],
dw.[UID_DialogWatchOperation], dw.[UID_DialogColumn], dw.[OldValue],
dwo.operationDate, dwo.DisplayValue, dwo.OperationType, dwo.ObjectKey
FROM [INGIDMTST].[dbo].[DialogWatchProperty] dw
INNER JOIN [INGIDMTST].[dbo].[DialogWatchOperation] dwo ON dw.UID_DialogWatchOperation = dwo.UID_DialogWatchOperation
INNER JOIN UNSAccountB u ON dwo.ObjectKey = u.ObjectKey
INNER JOIN Person p ON p.uid_person = u.uid_person
where OperationType = 'U'
And dwo.ObjectKey = ( select XObjectKey
from UNSAccountB
where UID_Person = ( select UID_Person
from Person
where PersonnelNumber = '50256' ) )
)t
where seq=1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

emmim44
Yak Posting Veteran

65 Posts

Posted - 2012-07-12 : 10:57:00

quote:

Originally posted by visakh16


SELECT *
FROM
(
SELECT ROW_NUMBER() OVER(PARTITION BY dw.UID_DialogColumn order by dwo.operationDate desc) AS Seq,
p.lastname, p.firstname, u.xproxy, dw.[UID_DialogWatchProperty],
dw.[UID_DialogWatchOperation], dw.[UID_DialogColumn], dw.[OldValue],
dwo.operationDate, dwo.DisplayValue, dwo.OperationType, dwo.ObjectKey
FROM [INGIDMTST].[dbo].[DialogWatchProperty] dw
INNER JOIN [INGIDMTST].[dbo].[DialogWatchOperation] dwo ON dw.UID_DialogWatchOperation = dwo.UID_DialogWatchOperation
INNER JOIN UNSAccountB u ON dwo.ObjectKey = u.ObjectKey
INNER JOIN Person p ON p.uid_person = u.uid_person
where OperationType = 'U'
And dwo.ObjectKey = ( select XObjectKey
from UNSAccountB
where UID_Person = ( select UID_Person
from Person
where PersonnelNumber = '50256' ) )
)t
where seq=1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Good one dude... "ROW_NUMBER() OVER(PARTITION BY" does?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-12 : 11:01:18

it groups table data onto horizontal groups based on columns given on partition by. then inside each group it generates a row number based on order specified on order by clause. what i've done above is to group table horizontally based on dlalogcolumn column value and then order it within based on descending order of operationdate. so latest record for each dialogcolumn has 1, next latest 2,...

filtering this on seq=1 will give you latest record for each dialogcolumn

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

emmim44
Yak Posting Veteran

65 Posts

Posted - 2012-07-12 : 11:14:05

quote:
Originally posted by visakh16

it groups table data onto horizontal groups based on columns given on partition by. then inside each group it generates a row number based on order specified on order by clause. what i've done above is to group table horizontally based on dlalogcolumn column value and then order it within based on descending order of operationdate. so latest record for each dialogcolumn has 1, next latest 2,...

filtering this on seq=1 will give you latest record for each dialogcolumn

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Thank you very much....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources