quote:

---

Originally posted by tarz

Thanks. I know it is hard to test on your machine..
But it still shows all the records. It shows the latest one first.. but the others are displayed as well.

---

quote:

---

Originally posted by tarz

Hi.. Thanks for your help.. Please see the structure of the tables.. I hope it is more clear now..

The TbMain table
Has ID column that contains unique ids.
ID|Member ( not unique)
12345 12121
56843 12121
45844 156476

The SI table has ids (not unique)
SI table:
ID | SR_ID
12345 1
56843 4
12345 6
56843 46
12345 23

The SR
It will search and will grab ONE RECORD based on the SR_ID column and the latest date_received.
Note: might have the same date_recieved so would just grab the first one..

Thank you

---

declare @MEMBER_ID varchar(20) 
set @MEMBER_ID ='11212'
Select TbMain.ID, tbUser.EMAIL_ADDR, t.COMPANY, t.REQUESTOR_NAME, t.DATE_RECEIVED 
FROM tbMain

LEFT JOIN tbUser
ON TbMain.ID = tbUser.ID
INNER JOIN 
(
SELECT 
SI.ID, 
SR.COMPANY, 
SR.REQUESTOR_NAME,
SR.DATE_RECEIVED
FROM (SELECT ID,MIN(SR_ID)AS MinID FROM SI GROUP BY ID)SI
INNER JOIN SR ON SI.MinID= SR.SR_ID
)t ON t.ID = TbMain.ID 
Where TbMain.MEMBER_ID = @MEMBER_ID 
ORDER BY t.DATE_RECEIVED DESC

       

quote:

---

Originally posted by tarz

yes the SR table has only unique SR_ID values.

I appreciate you helping me out..
It is almost done. IT is now grabbing one record!! But there is small bug in the FROM clause:


INNER JOIN
(
SELECT SI.ID, SR.COMPANY, SR.REQUESTOR_NAME, SR.DATE_RECEIVED
FROM ( SELECT ID,MIN(SR_ID)AS MinID FROM SI GROUP BY ID ) SI
INNER JOIN SR ON SI.MinID= SR.SR_ID
)

It is grabbing the minimum number.. This is not correct. I need to grab the latest based on DATE_RECEIVED
And not the smallest number.

i.e If I run this query:
SELECT SI.ID, SR.DATE_RECEIVED, SR.SR_ID
FROM SI
INNER JOIN SR ON SI.SR_ID = SR.SR_ID
WHERE SI.ID= '05'
order by SR.DATE_RECEIVED desc

I would get:
ID DATE_RECEIVED SR_ID
------------------------------------------------ --------------------------- ------------------
05 2002-07-02 00:00:00.000 4511
05 2002-07-04 00:00:00.000 4539
05 2003-03-12 00:00:00.000 6837
05 2006-01-26 00:00:00.000 20087
05 2006-05-23 00:00:00.000 21878
05 2005-05-19 00:00:00.000 8003062

The code you posted would give me the first record because the SR_ID is the smallest number.
I would need to get the SR_ID = 21878 because it is the latest based on the date.

Thank you!!

---

INNER JOIN 
(
SELECT SI.ID, SR.COMPANY, SR.REQUESTOR_NAME, SR.DATE_RECEIVED
FROM (    SELECT ID,MIN(SR_ID)AS MinID FROM SI GROUP BY ID  ) SI
INNER JOIN SR ON SI.MinID= SR.SR_ID
INNER JOIN (SELECT SR_ID,MAX(DATE_RECEIVED) AS MaxDate 
FROM SR 
GROUP BY SR_ID)tmp
ON tmp.SR_ID=SR.SR_ID
AND tmp.MaxDate=SR.DATE_RECEIVED
)

quote:

---

Originally posted by tarz

I changed it . and it still grabs the record with the smallest SR_ID value..
My quess it is to do with ...MIN(SR_ID)AS MinID... Why do we need it there? since it can be always different.. we need to retrieve based on MAX(DATE_RECEIVED)...

OMg why is it so complicated..

Thank you very much for help me out..

---

quote:

---

Originally posted by tarz

Let me try again!

MemberID gets entered "11212"
|
|
Gets all the ids from tblMain table where memberID is 11212. The Ids are unique!!
Output:
ID
--
12012
89890
89890
797223..etc..
|
|
Then Get the SR_ID from SI table using the ID found in TblMain table.
the SI table can have multiple same IDs. Also join SR table using the SR_ID to get the rest of the data
OUtput:
--
ID SR_ID |(data from SR table) DATE_RECEIVED | Requestor Name.. etcc..
12012 2323 2002-07-02 00:00:00.000 ...etc
12012 2311 2005-07-02 00:00:00.000
89890 8789 ..etc
89890 23245
89787 98976

|
|
One record for each ID will be returned.. based on the latest DATE_RECEIVED..
The output: for ID 12012 would be..

ID SR_ID |(data from SR table) DATE_RECEIVED | Requestor Name.. etcc..
12012 2311 2005-07-02 00:00:00.000
..ect..

I hope is it more clear now!!

Thank you

---

quote:

---

Originally posted by tarz

oopss sorry I meant to write different IDs for TblMain table.

---