two_pros.sql

SELECT
	*
FROM
	wp_posts
	INNER JOIN wp_postmeta ON wp_posts.ID = wp_postmeta.post_id
	LEFT JOIN (
		SELECT
			wp_posts.ID
		FROM
			wp_posts
			INNER JOIN wp_postmeta ON wp_posts.ID = wp_postmeta.post_id
				WHERE (wp_postmeta.meta_key = 'smoker' AND wp_postmeta.meta_value = 'Yes')
	) smoker on smoker.id = wp_posts.ID
WHERE
	smoker.ID IS NOT NULL
	AND wp_posts.post_type = 'volunteer'
	AND wp_posts.post_status = 'publish'
	AND(wp_postmeta.meta_key = '_state'
		AND wp_postmeta.meta_value = 'available')