Join problem....

[boardtc]

I'm sorry I'm not able to simplify this to make my question easier to understand.

In the below query there are can be more than 1 conditions records returned for each person (if there are duplicate max COND_TrainingStatusRO). I only want 1 per person. So say there are 10 people and for 1 person there are 2 conditions and 1 for the rest, this returns 11 records instead of 10.

If there are more than 1, I can distinguish them by getting the Max LastUpdated (commented out below). I can't get LastUpdated to equate to the max for each person...using the example above I might get 4 records with the below rather than 10.

I completely realise this might make no sense...but any ideas?

The query may well be optimisable but this is what my sql 'skills' are giving me!

query :

select
Per_FirstName,Per_LastName, COND_TrainingStatusRO

from Players

INNER JOIN Persons ON Players.OID = Persons.OID

INNER JOIN AssPlayerGroupPlayers ON Players.OID = AssPlayerGroupPlayers.APYGP_PlayerOID

INNER JOIN PlayerGroups ON AssPlayerGroupPlayers.APYGP_PlayerGroupOID = PlayerGroups.OID

inner Join PlayerCustoms ON PlayerCustoms.Owner_OID = Players.OID

LEFT OUTER Join Consultations ON PlayerCustoms.OID = Consultations.Owner_OID

LEFT OUTER Join Conditions ON Consultations.OID = Conditions.Owner_OID

where
(Players.Owner_OID = '{00000000-0000-0000-0000-000000000000}') and

(
(
Consultations.Cons_Date in (
( Select Max(Cons_Date)
from Consultations
where Owner_Oid = Players.OID
)
) or
Consultations.Cons_Date is null
) and

(
Conditions.Cond_TrainingStatusRO in (
( Select Max(Cond_TrainingStatusRO) from Conditions
where Owner_Oid = Consultations.OID
)
) or
Conditions.Cond_TrainingStatusRO is null
) and

-- conditions.LastUpdated = (
-- ( select Max(LastUpdated)
-- from conditions
-- where Owner_Oid = Consultations.OID
-- )
-- )
-- or
-- Conditions.LastUpdated is null
-- and


(PlayerGroups.PYG_Title = 'Development 1') and
(AssPlayerGroupPlayers.APYGP_Status = 0)
)

 

[rac2]

Maybe this line of thinking will take you there.

...
LEFT OUTER Join Conditions ON
   Consultations.OID = Conditions.Owner_OID
   AND (
         conditions.LastUpdated = (
              SELECT MAX(LastUpdated) 
              FROM conditions
              WHERE Owner_Oid = Consultations.OID
             ) 
         OR 
         Conditions.LastUpdated IS NULL
       )
...

You can make more complicated JOIN criteria than the simple a.id = b.id. So add the condition that the LastUpdated date is the maximum date for the particular Consultation.OID. This is a correlated subquery.

It is as if you had a table with just one condition for each Consultation.OID, the latest one. In fact, you could define a VIEW with just those rows and JOIN the view instead of the Conditions table. Doing so might make the query easier to read and easier to write.

"I just dropped in to see what condition my condition was in."

 

[ESquared]

And as always I'll give my plug for derived tables' superiority over correlated subqueries, even in terms of clarity.

SELECT
	Per_FirstName,
	Per_LastName,
	COND_TrainingStatusRO
FROM Players PL
	INNER JOIN Persons PE ON PL.OID = PE.OID  
	INNER JOIN AssPlayerGroupPlayers A ON PL.OID = A.APYGP_PlayerOID  
	INNER JOIN PlayerGroups G ON A.APYGP_PlayerGroupOID = G.OID 
	INNER JOIN PlayerCustoms PC ON PC.Owner_OID = PL.OID 
	LEFT JOIN (
		Consultations S
		INNER JOIN (
			SELECT Owner_Oid, MaxCons = Max(Cons_Date)
			FROM Consultations
			GROUP BY Owner_Oid
		) SMax ON S.Owner_OID = SMax.Owner_OID AND S.Cons_Date = SMax.MaxCons
	) ON PC.OID = S.Owner_OID
	LEFT JOIN (
		Conditions D
		INNER JOIN (
			SELECT Owner_Oid, MaxCond, MaxUpdated = Max(LastUpdate)
			FROM Conditions C1
				INNER JOIN (
					SELECT Owner_Oid, MaxCond = Max(Cond_TrainingStatusRO)
					FROM Condition
					GROUP BY Owner_Oid
				) C2 ON C1.Owner_Oid = C2.Owner_Oid AND C1.Cond_TrainingStatusRO = C2.MaxCond
			GROUP BY Owner_Oid, MaxCond
		) DMax ON D.Owner_Oid = DMax.Owner_OID AND D.Cond_TrainingStatusRO = DMax.MaxCond AND D.LastUpdate = DMax.MaxUpdated
	) ON S.OID = D.Owner_OID  
WHERE
	PL.Owner_OID = '{00000000-0000-0000-0000-000000000000}'
	AND G.PYG_Title = 'Development 1'
	AND A.APYGP_Status = 0

Not that this is a total cinch to understand...

-------------------------------------
It is better to have honor than a good reputation.
(Reputation is what other people think about you. Honor is what you know about yourself.)

 

[boardtc]

Gentlemen - I bow my head to your sick skills!

rac2 I started testing with your solution, it returned the correct number of rows (74)! But COND_TrainingStatusRO was returning 3 nulls where it shouldn't be...haven't figured out why it's not working yet...

esquared...i plugged in your code and with 2 C1.Conditions instead of Conditions it worked first time!!!!

i'd love to be able to see the sql like this in my head!

having though about it a bit and read up some...i'm tending to agree that understanding the derived table concept might be easier that correlated subqueries (rac, thanks for the example on them)...

 

[boardtc]

btw, why the groups bys?

 

[SQLSister]

If you have an aggretate field and one or more ordinary fields in the same select, then you must group by the ordinary fields or use aggregates in their place.

Questions about posting. See faq183-874

 

[ESquared]

To understand what is going on, run each derived table as its own query. In Query Analyzer, highlight just the section you want to run and press F5.

-------------------------------------
It is better to have honor than a good reputation.
(Reputation is what other people think about you. Honor is what you know about yourself.)

 

[boardtc]

Thanks for the info, very useful.

Esquared- testing just came back with a duplicate being produced, 2 records for the same person with the same Cond_TrainingStatusRO. I've reproduced it myself running (your) the query....not out of the woods yet! will have to dig more tomorrow

cheers, tom.

 

[ESquared]

Is the LastUpdate value for the two records the same?

-------------------------------------
It is better to have honor than a good reputation.
(Reputation is what other people think about you. Honor is what you know about yourself.)

 

[boardtc]

Panacing i was at the end of the day when i thought it had all been solved and then saw this example!

yep duplicate LastUpdated fields.

Actually I only started using LastUpdated because sometimes there are (legitimatly) duplicate Cond_TrainingStatusRO and I only want one record per person - when there are duplicate Cond_TrainingStatusRO I can take one of them, it doesn't matter which. Would there be a way of getting omly 1 record without using LastUpdated?

Thanks for the continued help, it's much appreciated and a great learning experience.

Cheers, Tom.

 

[ESquared]

The only way to fix this is to add a column which has a guaranteed unique value, such as an int identity column.

There is no LAST or FIRST summation function in SQL server so we have to use a Max() or Min() on some column or collection of columns.

I suppose that if all the data is exactly the same in the two rows, you can toss in a DISTINCT or a GROUP BY to collapse it into one row. Just put it in the derived table.

-------------------------------------
It is better to have honor than a good reputation.
(Reputation is what other people think about you. Honor is what you know about yourself.)

 

[boardtc]

Thanks for the tips. I tried :

LEFT JOIN (Conditions D
INNER JOIN (
SELECT distinct Owner_Oid, MaxCond = Max(Cond_TrainingStatusRO)
FROM Conditions
GROUP BY Owner_Oid
) DMax ON D.Owner_Oid = DMax.Owner_OID AND D.Cond_TrainingStatusRO = DMax.MaxCond
) ON S.OID = D.Owner_OID

But this still returns 2 rows even though the Owner_Oid & MaxCond are the same.

I played around with chnaging the group by but did not know how to impllemtn the change. I'll keep playing with it.

Thanks, Tom.

 

[ESquared]

Oh, yes, there are two rows... so even if the group by gets a unique selection you'll have to do something to differentiate them.

Until there is a way to distinguish one row from another, the only fix is a DISTINCT clause in the main query, which works but isn't (in my mind) the best way. The best way is to add a column such as an identity which will make the rows different.

-------------------------------------
It is better to have honor than a good reputation.
(Reputation is what other people think about you. Honor is what you know about yourself.)

 

[boardtc]

Thanks a lot for the extra info. In this case if all the returned values are equal i'm happy with either row. I'm curious what your argument against distinst is... is it because it's just not exact enough and not "pure"...?

Thanks again, tom.

 

[ESquared]

Well, for example, what do you do if you want to delete one of the rows? If they are identical, the only way is to set rowcount to 1 and do the delete one at a time. No mass deletion of deuplicate rows.

There are other reasons I could probably come up with but that's a good enough one for now.

-------------------------------------
It is better to have honor than a good reputation.
(Reputation is what other people think about you. Honor is what you know about yourself.)

 

[boardtc]

interesting, ta for the post. we display the above list in a listview which allows them to navigate through players in the system. If they choose delete on one of the entries, we have a separate mapper that deals with deletes so this wouldn't be a problem for us methinks. cheers, tom.