Need Help joining field from another table

1

Hello,
I am working with multiple tables that are basically linked with EmpNum and Record Date mostly. I have a note table for which I need the comments that have max SEQ number. Unfortunately, I am not able to find any column field in that table to group by or use max function other than using a field from another table that has data_ID referring to each occurrence of that service.

Table A
Data_ID Record_Date EmpNum Rec_Type
239 2016-10-24 18:09:41.7274800 123 EMP
240 2016-10-24 18:09:41.7274800 123 EMP

Table B
Record_Date EmpNum SEQ Note
2016-10-24 18:09:41.7274800 123 2 For Emp Only
2016-10-24 18:09:41.7274800 123 3 Do Not Open
2016-10-24 18:09:41.7274800 123 4 Discard after open
2016-10-24 18:09:41.7274800 123 2 For Emp Only
2016-10-24 18:09:41.7274800 123 3 Do Not Open
2016-10-24 18:09:41.7274800 123 4 Discard after open

Table Z
EmpNum Record_Date Rec_Type
123 2016-10-24 18:09:41.7274800 EMP

So from B table I need the comments that have max seq value per Data_ID field from Table A
Unfortunately, I am not able to find any column field in table B to group by the records

Data_ID Record_Date EmpNum Rec_Type B.SEQ B.Comment
239 2016-10-24 18:09:41.7274800 123 EMP 4 Discard after open
240 2016-10-24 18:09:41.7274800 123 EMP 4 Discard after open

Will appreciate the help in coaching the new bee
Thanks

2

You can't use columns that aren't there so you can add the missing column to table B or exclude it from the join criteria.


DECLARE @a 
   table(Data_ID int, Record_Date datetime2(7),  EmpNum int, Rec_Type char(3));
INSERT @a VALUES 
    (239, '20161024 18:09:41.72748', 123, 'EMP')
 ,  (240, '20161024 18:09:41.72748', 123, 'EMP');

DECLARE @b 
   table(Record_Date datetime2(7), EmpNum int, SEQ int, Note varchar(1000));
INSERT @b
VALUES
    ('2016-10-24 18:09:41.72748', 123, 2, 'For Emp Only')
  , ('2016-10-24 18:09:41.72748', 123, 3, 'Do Not Open')
  , ('2016-10-24 18:09:41.72748', 123, 4, 'Discard after open')
  , ('2016-10-24 18:09:41.72748', 123, 2, 'For Emp Only')
  , ('2016-10-24 18:09:41.72748', 123, 3, 'Do Not Open')
  , ('2016-10-24 18:09:41.72748', 123, 4, 'Discard after open');
SELECT a.Data_ID
     , a.Record_Date
     , a.EmpNum
     , a.Rec_Type
     , Max(b.SEQ) SEQ
     , b.Note
FROM @a a
JOIN @b b 
ON a.EmpNum = b.EmpNum
AND a.Record_Date = b.Record_Date
WHERE b.SEQ = (SELECT top (1) Max(SEQ) FROM @b GROUP BY SEQ)
GROUP BY a.Data_ID
       , a.Record_Date
       , a.EmpNum
       , a.Rec_Type
       , b.Note;

image

3

Thank you so much for explaining this. I will try it out