<div>
This is what you need- 
 
Use NOT EXISTS instead of NOT IN. NOT IN does not consider NULLs and slow also. 
 

<pre><code class="code-10 nolinks" id="code-20-42356634-1">SELECT * FROM 
(
	SELECT
	*
	FROM
	dbo.[qry_HorsesANDRidersAssociations]
	Group By ClientID, HorseID, HorseName
	UNION ALL
	SELECT * FROM 
	dbo.[qry_HorsesRidersHaveCompeted]
	Group BY ClientID, HorseID, HorseName
)k
WHERE NOT EXISTS ( select NULL from tblHideHorse h WHERE h.ClientID = r.ClientID )
</code></pre>
</div>

<div>
Sorry must be being thick - pasted your code and it doesnt work. 
 
Multi-part identifier r.ClientId could not be bound? 
 
in the horsehide table it has to be an exact match for horse and rider together. Not just any horse or any rider existing in that table? 
 
does that make sense?
</div>

<div>
can you provide few rows with column names of &nbsp;[qry_HorsesANDRidersAssociations] , dbo.[qry_HorsesRidersHaveCompeted] &nbsp;and tblHideHorse &nbsp;so that we will give u exact output ?
</div>

<div>
I think at a minimum, this: 
 
r.ClientID 
 
should have been: 
 
k.ClientID 
 

<hr />
»bp
</div>

<div>
Thank you very much - I would never have gotten there on my own!
</div>

<div>
Welcome 
glad to help as always :)
</div>

<div>
<div class="content wysiwyg-content">
I have the following query producing a recordset drawn from 2 tables. 
 

<pre><code class="code-1 nolinks" id="code-10-29066769-1">SELECT
*
FROM
dbo.[qry_HorsesANDRidersAssociations]
Group By ClientID, HorseID, HorseName
UNION ALL
SELECT * FROM 
dbo.[qry_HorsesRidersHaveCompeted]
Group BY ClientID, HorseID, HorseName
</code></pre>
 
It produces a list of horses that a rider &quot;wants&quot; to have associated on their account and also a list of horses they have actually ridden. 
 
I have been asked for a facility to hide horses from a riders account - which is more applicable to the ones they have actually ridden rather than associated (as they can easily remove this association) 
 
A new table tblHideHorse has been created with HorseID and ClientID along with HorseHideID (identity) 
 
How can I combine this into the above query to produce the list minus the horses they select to not show? 
 
I know it is something along the lines of 
 
where clientid not in (select ClientID from tblHideHorse) but im afraid that is the limit of my knowledge. 
 
 
Thanks in advance 
 
MSSQL server 2014
</div>
</div>

I have the following query producing a recordset drawn from 2 tables.

(CODE)

It produces a list of horses that a rider "wants" to have associated on their account and also a list of horses they have actually ridden.

I have been asked for a facility to hide horses from a riders account - which is more applicable to the ones they have actually ridden rather than associated (as they can easily remove this association)

A new table tblHideHorse has been created with HorseID and ClientID along with HorseHideID (identity)

How can I combine this into the above query to produce the list minus the horses they select to not show?

I know it is something along the lines of

where clientid not in (select ClientID from tblHideHorse) but im afraid that is the limit of my knowledge.


Thanks in advance

MSSQL server 2014

SQL Code Help Please

SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.