?
Solved

Select and Join question?

Posted on 2010-01-09
11
Medium Priority
?
169 Views
Last Modified: 2012-05-08
How do I get this to work. I am getting an error on the 'RecordKey=h.SalesHeaderID', it does not like the h.SalesHeaderID.

select h.salesheaderid, h.ship_customer, h.DeliveryDate, x.oldvalue,x.newvalue,x.updatedate
from tblSalesHeader h left join (select top 1 * from Audit where RecordKey=h.SalesHeaderID order by updatedate desc) x on x.recordkey = h.salesheaderid
0
Comment
Question by:Jess31
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
11 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 26275893
Try it this way:

SELECT  h.salesheaderid,
        h.ship_customer,
        h.DeliveryDate,
        x.oldvalue,
        x.newvalue,
        x.updatedate
FROM    tblSalesHeader h
        LEFT JOIN (SELECT      TOP 1
                            oldvalue,
                                          newvalue,
                                          updatedate
                   FROM     Audit
                   -- WHERE    RecordKey = h.SalesHeaderID
                   ORDER BY updatedate DESC
                  ) x ON x.recordkey = h.salesheaderid
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 26275898
While my solution is syntactically correct, I suspect you are looking for something quite different using a CTE.
0
 
LVL 5

Expert Comment

by:chinawal
ID: 26275904
Try this

select h.salesheaderid, h.ship_customer, h.DeliveryDate, x.oldvalue,x.newvalue,x.updatedate
from tblSalesHeader h
      left join
            (select top 1 a.*
             from Audit a
             inner join tblSalesHeader h
             on a.RecordKey = h.SalesHeaderID
             order by a.updatedate desc) x
 on x.recordkey = h.salesheaderid
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 26275909
On second thoughts, I suspect this is what you want:
SELECT  h.salesheaderid,
        h.ship_customer,
        h.DeliveryDate,
        x.oldvalue,
        x.newvalue,
        x.updatedate
FROM    tblSalesHeader h
            LEFT JOIN (
                        SELECT      oldvalue,
                                    newvalue,
                                    updatedate
                        FROM      Audits a
                                    INNER JOIN (
                                          SELECT      MAX(updatedate) Lastupdatedate
                                          FROM      Audits
                                          GROUP BY
                                                      RecordKey) d ON a.updatedate = d.Lastupdatedate
                        ) x ON h.salesheaderid = x.recordkey
0
 
LVL 1

Author Comment

by:Jess31
ID: 26275952
For some reason non of these seem to work.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 26275962
Fair enough.
0
 
LVL 41

Expert Comment

by:Sharath
ID: 26276357
try this.
;with CTE as (
select salesheaderid, ship_customer, DeliveryDate, oldvalue,newvalue,updatedate,
       row_number() over (partition by x.SalesHeaderID order by x.updatedate desc) as rn
  from tblSalesHeader h
  left join Audit x on x.recordkey = h.salesheaderid)
select h.salesheaderid, h.ship_customer, h.DeliveryDate, x.oldvalue,x.newvalue,x.updatedate
  from CTE 
 where rn = 1

Open in new window

0
 
LVL 41

Expert Comment

by:Sharath
ID: 26276359
sorry, this one.
;with CTE as (
select h.salesheaderid, h.ship_customer, h.DeliveryDate, x.oldvalue,x.newvalue,x.updatedate,
       row_number() over (partition by x.SalesHeaderID order by x.updatedate desc) as rn
  from tblSalesHeader h
  left join Audit x on x.recordkey = h.salesheaderid)
select salesheaderid, ship_customer, DeliveryDate, oldvalue,newvalue,updatedate
  from CTE 
 where rn = 1

Open in new window

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 26278036
Jess31,

A word of caution, before you choose to use ROW_NUMBER() over a simple derived table, I would urge you to read the following article:

Using row_number() vs. a correlated sub-select
http://sqlservernation.com/blogs/brandongalderisi/archive/2009/09/20/using-row-number-vs-a-correlated-sub-select.aspx
0
 
LVL 41

Expert Comment

by:Sharath
ID: 26279022
try this if you do not want to use ROW_NUMBER
select h.salesheaderid, h.ship_customer, h.DeliveryDate, x.oldvalue,x.newvalue,x.updatedate,
       row_number() over (partition by x.SalesHeaderID order by x.updatedate desc) as rn
  from tblSalesHeader h
  left join (select t1.recordkey,t1.oldvalue,t1.newvalue,t1.updatedate 
               from Audit t1 
               join (select recordkey,max(updatedate) as updatedate from Audit group by recordkey) as t2 
                 on t1.recordkey = t2.recordkey and t1.updatedate = t2.updatedate) as x 
    on x.recordkey = h.salesheaderid

Open in new window

0
 
LVL 41

Accepted Solution

by:
Sharath earned 2000 total points
ID: 26279034
This is another approach.
select h.salesheaderid, h.ship_customer, h.DeliveryDate, x.oldvalue,x.newvalue,x.updatedate,
       row_number() over (partition by x.SalesHeaderID order by x.updatedate desc) as rn
  from tblSalesHeader h
  left join (select t1.recordkey,t1.oldvalue,t1.newvalue,t1.updatedate 
               from Audit t1 
              where updatedate = (select max(updatedate) from Audit t2 where t1.recordkey = t2.recordkey)) as x 
    on x.recordkey = h.salesheaderid

Open in new window

0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question