Taking my SQL View a step further

Hi Experts,

you very kindly helped me out on a view SQL code a few days ago.

The code is below

SELECT        ClientID, HorseName, HorseID
FROM            (SELECT        ClientID, HorseName, HorseID
                          FROM            dbo.qry_HorsesANDRidersAssociations
                          GROUP BY ClientID, HorseID, HorseName
                          UNION ALL
                          SELECT        ClientID, HorseName, HorseID
                          FROM            dbo.qry_HorsesRidersHaveCompeted
                          GROUP BY ClientID, HorseID, HorseName) AS r
WHERE        (NOT EXISTS
                             (SELECT        NULL AS Expr1
                               FROM            dbo.tblHideHorse AS h
                               WHERE        (ClientID = r.ClientID) AND (HorseID = r.HorseID)))

Open in new window


This basically takes information from 2 views in my database where riders who are associated with horses they have either
Ridden or chosen to be associated with for the purpose of entering into competitions.

The modification you did was to allow the rider to specifically EXCLUDE a horse from their list when their ClientID AND the HorseID were present together in a row in a separate table tblHideHorse.

What I would like to do now is go a stage further and generate a view that still shows all the above information but if that record exists in tblHideHorse (and consequently excludes the relationship from this view as it stands now) I would like it rather to create a column to be that has either 1 - horse is blocked (i.e horseID and ClientID exist in tblHideHorse) or 0 - horse is not blocked (i.e horseID and ClientID does not exist in tblHideHorse).

Both the HorseID AND ClientID must be in tblHideHorse for it to be valid.

The purpose of this is so that in their client screen they can immediately see all "their" horses along with the status or 'shown' or 'not shown' so they can modify as they wish.

Hope you can help - thanks in advance
roblickleyAsked:
Who is Participating?
 
Pawan KumarDatabase ExpertCommented:
Do not use UNION, it will kill the performance.  Also dont know whether it will work or not in this case. Please  use below-
SELECT r.ClientID, r.HorseName, r.HorseID, MAX(CASE WHEN h.ClientID IS NULL OR h.HorseID IS NULL THEN 0 ELSE -1 END) BlockingHorse
FROM 
(
	SELECT ClientID, HorseName, HorseID
	FROM   dbo.qry_HorsesANDRidersAssociations
	GROUP BY ClientID, HorseID, HorseName
	UNION ALL
	SELECT ClientID, HorseName, HorseID
	FROM   dbo.qry_HorsesRidersHaveCompeted
	GROUP BY ClientID, HorseID, HorseName
) r
LEFT JOIN dbo.tblHideHorse h ON h.ClientID = r.ClientID AND h.HorseID = r.HorseID
GROUP BY r.ClientID, r.HorseName, r.HorseID

Open in new window

0
 
Pawan KumarDatabase ExpertCommented:
Please try this -

SELECT r.* , CASE WHEN h.ClientID IS NULL OR h.HorseID IS NULL THEN 0 ELSE -1 END BlockingHorse
FROM 
(
	SELECT ClientID, HorseName, HorseID
	FROM   dbo.qry_HorsesANDRidersAssociations
	GROUP BY ClientID, HorseID, HorseName
	UNION ALL
	SELECT ClientID, HorseName, HorseID
	FROM   dbo.qry_HorsesRidersHaveCompeted
	GROUP BY ClientID, HorseID, HorseName
) r
LEFT JOIN dbo.tblHideHorse h ON h.ClientID = r.ClientID AND h.HorseID = r.HorseID

Open in new window

1
 
roblickleyAuthor Commented:
So it works - but there are duplicates.

Here is why duplicates occur.

The rider might, in the first instance, associate the horse with their account to make the entry.

Once the entry is made that will also show that relationship through the HorsesRidersHaveCompeted view.

So in fact once the very first entry is made for that horse, there are in fact TWO relationships between horse and rider.

Can you filter them out to just show one instance per horse per rider?
0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
use a union instead of a unionall.
0
 
roblickleyAuthor Commented:
Thank you!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.