Ask Ben: Pulling Unique SQL Records That Match ALL Join Conditions 

<!--- Param form value. --->
<cfparam
	name="FORM.list_trait_id"
	type="regex"
	pattern="[\d,]*"
	default=""
	/>
 
 
<!--- Check to see if we have any trait. --->
<cfif Len( FORM.list_trait_id )>
 
	<!--- Query for girls. --->
	<cfquery name="qGirl" datasource="ben">
		SELECT
			g.id,
			g.name
		FROM
			girl g
		INNER JOIN
			(
 
				<!---
					Get only girl IDs from the JOIN table where
					ALL of the trait IDs match. Because we know
					that the JOIN records are unique, we know
					that the COUNT(*) on the GROUP BY must equal
					the length of the trait ID list.
				--->
				SELECT
					gtjn.girl_id
				FROM
					girl_trait_jn gtjn
				WHERE
					gtjn.trait_id IN ( <cfqueryparam value="#FORM.list_trait_id#" cfsqltype="cf_sql_integer" list="true" /> )
				GROUP BY
					gtjn.girl_id
				HAVING
					COUNT( * ) = <cfqueryparam value="#ListLen( FORM.list_trait_id )#" cfsqltype="cf_sql_integer" />
 
			) AS filter
		ON
			g.id = filter.girl_id
		ORDER BY
			g.name ASC
	</cfquery>
 
</cfif>
 
 
<!--- Query for all traits. --->
<cfquery name="qTrait" datasource="ben">
	SELECT
		id,
		name
	FROM
		trait
	ORDER BY
		name ASC
</cfquery>
 
 
<cfoutput>
 
	<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
	<html>
	<head>
		<title>Select Records My ID Set Matching</title>
	</head>
	<body>
 
		<h1>
			Select Traits
		</h1>
 
		<form action="#CGI.script_name#" method="post">
 
			<p>
				<cfloop query="qTrait">
 
					<label>
 
						<input
							type="checkbox"
							name="list_trait_id"
							value="#qTrait.id#"
							<cfif ListFind( FORM.list_trait_id, qTrait.id )>
								checked="true"
							</cfif>
							/>
 
						#qTrait.name#
 
					</label>
					<br />
 
				</cfloop>
			</p>
 
			<p>
				<input type="submit" value="Get Girls!" />
			</p>
 
		</form>
 
 
		<!--- Check to see if we have a search. --->
		<cfif Len( FORM.list_trait_id )>
 
			<h2>
				Matching Girls
			</h2>
 
			<ul>
				<cfloop query="qGirl">
 
					<li>
						#qGirl.name#
					</li>
 
				</cfloop>
			</ul>
 
		</cfif>
 
	</body>
	</html>
 
</cfoutput>

For Cut-and-Paste