Cacher is the code snippet organizer for pro developers

-- example showing how to pull both the hard creditee number and name from the T_CONTRIBUTION table as well as the soft creditee number from the T_CREDITEE table, and then tack on the creditee name from T_CUSTOMER
SELECT a.customer_no,
	c.creditee_no 'Creditee Number',
	ISNULL(c.creditee_no,a.customer_no) AS 'Lowest Creditee',
	b.lname AS 'Hard Credit Name',
	d.lname 'Soft Credit Name',
	ISNULL(d.lname,b.lname) AS 'Lowest Name', 
	a.ref_no,
	a.recd_amt,
	a.cont_amt
	FROM T_CONTRIBUTION a (nolock)
JOIN T_CUSTOMER b (nolock) ON a.customer_no = b.customer_no
LEFT JOIN T_CREDITEE c on a.ref_no = c.ref_no
LEFT JOIN T_CUSTOMER d (nolock) ON c.creditee_no = d.customer_no
WHERE a.customer_no IN(44933,225187) AND cont_amt > 0

/* Results:
customer_no Creditee Number Lowest Creditee Hard Credit Name                  Soft Credit Name                Lowest Name                   ref_no      cont_dt           cont_amt
----------- --------------- --------------- --------------------------------  -----------------------------   ----------------------------- ----------- -----------------------------------
44933       NULL            44933           Joe and Susan Smith Household     NULL                            Joe and Susan Smith Household 7301885     2013-09-25        00:00:00.000 21249.00
225187      44933           44933           The Giving Foundation             Joe and Susan Smith Household   Joe and Susan Smith Household 4069202     2006-12-31 00:00:00.000 35000.00
225187      44933           44933           The Giving Foundation             Joe and Susan Smith Household   Joe and Susan Smith Household 4069238     2006-12-31 00:00:00.000 100000.00
225187      NULL            225187          The Giving Foundation             NULL                            The Giving Foundation         4080386     2006-12-31 00:00:00.000 16231.75
*/

---------------
-- DETAIL!!! --
---------------

-- SELECT customer_no from T_CONTRIBUTION
SELECT a.customer_no,
  -- this is an optional column, you don't need it to get the 'Lowest Creditee'. This selects the creditee_no from the T_CREDITEE table
	c.creditee_no 'Creditee Number',
	-- pulls the soft-creditee name if it exists (is not null), or the hard-creditee name if there isn't a soft-creditee. Uses ISNULL, if c.creditee_no is NULL (meaning there is no soft-creditee), instead pull the usual a.customer_no
	ISNULL(c.creditee_no,a.customer_no) AS 'Lowest Creditee',
	-- pulls lname from T_CUSTOMER table
	b.lname AS 'Hard Credit Name',
	-- this is an optional column, you don't need it to get the 'Lowest Name'. This selects the lname from the T_CUSTOMER table d (the second join)
	d.lname 'Soft Credit Name',
	-- pulls the soft-creditee name if it exists (is not null), or the hard-creditee name if there isn't a soft-creditee. Uses ISNULL, if d.lname is NULL (meaning there is no soft-creditee), instead pull the usual a.lname
	ISNULL(d.lname,b.lname) AS 'Lowest Name', 
	-- select ref_no, cont_dt, recd_amt, and cont_type from T_CONTRIBUTION a
	a.ref_no,
	a.cont_dt,
	a.cont_amt
	-- select FROM T_CONTRIBUTION a
	FROM T_CONTRIBUTION a (nolock)
-- INNER JOIN T_CUSTOMER b to add on b.lname, will only return rows from T_CUSTOMER b that have a corresponding entry in T_CONTRIBUTION b
JOIN T_CUSTOMER b (nolock) ON a.customer_no = b.customer_no
-- LEFT JOIN T_CREDITEE c to add soft creditee no if it exists, return NULL if it doesn't exist
LEFT JOIN T_CREDITEE c on a.ref_no = c.ref_no
-- LEFT JOIN T_CUSTOMER d (again) to add soft creditee name f it exists, return NULL if it doesn't exist
LEFT JOIN T_CUSTOMER d (nolock) ON c.creditee_no = d.customer_no
-- filter by the customer_nos we're interested in looking at
WHERE a.customer_no IN(44933,225187) AND cont_amt > 0