Only need last record in relationship 1

[mjd3000]

Hi,

I have 3 tables that are related, Table 1 (User) is related to Table 2 (Review) and Table 2 is related to Table 3 (Country). The problem I have is that every time a User has a Review a new record is created in Review and new records are created in Country, and the previous records for that user in Review and Country now are no longer relevant. So I need to write some SQL where it only joins a User to his latest Review and Countries. Is this possible?

 

[djj55]

Look into TOP and ORDER BY DESC something like

SELECT TOP 1 ReviewDate 
FROM myReviews 
ORDER BY ReviewDate DESC

I hope this helps,

djj
The Lord is My Shepard (Psalm 23) - I need someone to lead me!

 

[mjd3000]

Hi,

But won't that just return 1 record? What I want to return is the last review PER USER.


Thanks,

Mike

 

[Joulius]

Hello!

You need something like this?

SELECT U.*, RD.*, CD.*
FROM USERS_TABLE U
INNER JOIN (
	SELECT user_id_fk, MAX(review_id) as last_review_id
	FROM REVIEWS_TABLE
	GROUP BY user_id_fk
) R ON U.user_id = R.user_id
INNER JOIN REVIEWS_TABLE RD ON R.last_review_id = RD.review_id
INNER JOIN (
	SELECT user_id_fk, MAX(country_id) as last_country_id
	FROM COUNTRIES_TABLE
	GROUP BY user_id
) C ON U.user_id = C.user_id_fk
INNER JOIN COUNTRIES_TABLE CD ON R.last_country_id = CD.country_id

I am assuming that REVIEWS_TABLE.review_id and COUNTRIES_TABLE.country_id are PKs.

 

[mjd3000]

Joulius,

When I run your SQL, I get the following error :

Operand data type uniqueidentifier is invalid for max operator

This error is because it won't allow MAX to be calculated on the review_id primary key. How do I get around this?

 

[Joulius]

How many rows do you have in REVIEWS_TABLE? Is it possible to:

ALTER TABLE REVIEWS_TABLE ADD rowid int IDENTITY(1,1)

so you can do an ORDER or MAX operation on that table?

 

[mjd3000]

These are Microsoft CRM views that I am not allowed to change....is there any other way to do this?

 

[Joulius]

Hi again and sorry for the delay.

Are there any columns in REVIEWS_TABLE that could be ordered (ex: the date when the review was created) or is the review_id initialized with NEWSEQUENTIALID()?
If not, that's really bad design and the only option that I see is:

CREATE TABLE #ORDERED_REVIEWS (row_id int identity(1,1), review_id uniqueidentifier)

INSERT INTO #ORDERED_REVIEWS (review_id)
SELECT review_id FROM REVIEWS_TABLE

SELECT U.*, RD.*, CD.*
FROM USERS_TABLE U
INNER JOIN REVIEWS_TABLE R ON U.user_id = R.user_id
	AND R.review_id = (SELECT TOP 1 review_id FROM #ORDERED_REVIEWS ORDER BY row_id DESC)
INNER JOIN (
    SELECT user_id_fk, MAX(country_id) as last_country_id
    FROM COUNTRIES_TABLE
    GROUP BY user_id
) C ON U.user_id = C.user_id_fk
INNER JOIN COUNTRIES_TABLE CD ON C.last_country_id = CD.country_id

DROP TABLE #ORDERED_REVIEWS

Hope this helps!

 

[mjd3000]

Joulius,

I tried something similar to your original post but using a datefield to get the last review rather than the reviewid. Here is my SQL :

select r.new_applicationformidname as 'Applicant Name',
a.new_programmeappliedforidname as 'Programme',
a.new_programmecontractidname as 'Programme Contract',
r.new_dateofreviewutc as 'Date Of Review',
sum(CASE WHEN e.new_gender = 1 AND
e.new_jobstatus = 0
THEN 1
ELSE 0 END) AS MaleCreated,
sum(CASE WHEN e.new_gender = 0 AND
e.new_jobstatus = 0
THEN 1
ELSE 0 END) AS FemaleCreated,
sum(CASE WHEN e.new_jobstatus = 0
THEN 1
ELSE 0 END) AS TotalCreated,
sum(CASE WHEN e.new_gender = 1 AND
e.new_jobstatus = 1
THEN 1
ELSE 0 END) AS MaleSafeguarded,
sum(CASE WHEN e.new_gender = 0 AND
e.new_jobstatus = 1
THEN 1
ELSE 0 END) AS FemaleSafeguarded,
sum(CASE WHEN e.new_jobstatus = 1
THEN 1
ELSE 0 END) AS TotalSafeguarded,
count(*) as TotalCreatedOrSafeguarded
from filterednew_applicationform a

INNER JOIN (
SELECT rd.new_applicationformid, MAX(rd.new_dateofreviewutc) as last_review_date
FROM filterednew_businessreview rd
GROUP BY rd.new_applicationformid
) rd on a.new_applicationformid = rd.new_applicationformid
INNER JOIN filterednew_businessreview r ON rd.last_review_date = r.new_dateofreviewutc

inner join dbo.FilteredNew_employeedetails e
on r.new_businessreviewid = e.new_businessreviewformid
group by r.new_applicationformidname, a.new_programmeappliedforidname,
a.new_programmecontractidname, r.new_dateofreviewutc

But it gives me reviews other than the last one. Can you see what I have done wrong?

 

[gmmastros]

try adding another condition to the join:

[tt][blue]INNER JOIN filterednew_businessreview r ON rd.last_review_date = r.new_dateofreviewutc
[!]and rs.new_applicationformid = r.new_applicationformid [/!][/blue][/tt]

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom