CODEWITHSUNDEEP
sql-serversql-server-2008
Gilbert V
Gilbert V

Reputation: 1040

SQL Pulling the latest information and information from another table

I have a record table that is recording changes within a table. I can pull the data from the first table fine, however when i try to join in another table to add some of its column information it stops displaying the information.

PartNumber | PartDesc | value | date
1          | test     | 1     | 3/4/2015

I wanted to include the Aisle tag's from the location table

PartNumber| AisleTag    | AisleTagTwo
1         | A1          | N/A

here is what i have as my sql statement so far

Select t1.PartNumber, t1.PartDesc , t1.NewValue ,  t1.Date,t2.AisleTag,t2.AisleTagTwo
from InvRecord t1
JOIN PartAisleListTbl t2 ON t1.PartNumber = t2.PartNumber
where Date = (select max(Date) from InvRecord where t1.PartNumber = InvRecord.PartNumber)
order by t1.PartNumber

it is coming up blank, my original sql statement doesn't include anything from t2. I am not sure what approach to go with in terms of getting the data combined any help is much appreciated thank you !

this should be the end result

PartNumber | PartDesc | value | date     | AisleTag    | AisleTagTwo
1          | test     | 1     | 3/4/2015 | A1          | N/A

Upvotes: 0

Views: 47

Answers (3)

xQbert
xQbert

Reputation: 35333

Not sure why your's isn't working... is date in both t1 and t2 by any chance?

Here's it re factored to use a inline view instead of a correlated query wonder if it makes a difference. 

Select t1.PartNumber, t1.PartDesc , t1.NewValue ,  t1.Date,t2.AisleTag,t2.AisleTagTwo
from InvRecord t1
JOIN PartAisleListTbl t2 
  ON t1.PartNumber = t2.PartNumber
JOIN (select max(Date) mdate, PartNumber from InvRecord GROUP BY PartNumber) t3
  on t3.partNumber= T1.PartNumber
 and T3.mdate = T1.Date 
order by t1.PartNumber

Upvotes: 1

Kittoes0124
Kittoes0124

Reputation: 5080

Pull the most recent row (based on Date) for each PartNumber in Table A and append data from Table B (joined on PartNumber):

SELECT *
FROM (
    SELECT A.PartNumber
         , A.PartDesc
         , A.NewValue
         , A.Date
         , B.AisleTag
         , B.AisleTagTwo
         , DateSeq = ROW_NUMBER() OVER(PARTITION BY A.PartNumber ORDER BY A.Date DESC)
    FROM InvRecord A
    LEFT JOIN PartAisleListTbl B
        ON A.PartNumber = B.PartNumber
) A
WHERE A.DateSeq = 1
ORDER BY A.PartNumber

Upvotes: 1

Ann L.
Ann L.

Reputation: 13965

Are you returning no records at all, or only records with AisleTag and AisleTagTwo as null?

Your sentence "it is coming up blank, my original sql statement doesn't include anything from t2." makes it sound like you're getting records with nulls for the t2 fields.

If you are, then you probably have a record in t2 that has nulls for those fields.

For troubleshooting purposes, try running the query without the WHERE clause:

Select t1.PartNumber, t1.PartDesc , t1.NewValue , t1.Date,t2.AisleTag,t2.AisleTagTwo
from InvRecord t1
JOIN PartAisleListTbl t2 ON t1.PartNumber = t2.PartNumber
order by t1.PartNumber

If you do get records, your problem is with the WHERE clause. If you don't, your problem is with the PartNumber fields in InvRecord and PartAisleListTbl not matching.

Upvotes: 1

Related Questions