- Customer Addresses Table
- One row in the table per address
- Addresses are categorized by a type of home, work, or other
- Question is to know how many of your customers work and live in different zip codes
- If either address is not present, then include the record
COALESCE(h.customer_id, w.customer_id) AS customer_id,
h.zip AS home_zip,
w.zip AS work_zip
customer_addr h LEFT OUTER JOIN
customer_addr w ON
h.customer_id = w.customer_id AND
h.addr_type = 'home' AND
w.addr_type = 'work'
(h.zip <> w.zip) OR h.zip IS NULL OR w.zip IS NULL.
Another, more efficient way to execute the same query in a single pass of the database.
MIN( CASE( WHEN ADDR_TYPE_CD = 'h' THEN zip END)) AS home_zip,
MIN( CASE( WHEN ADDR_TYPE_CD = 'w' THEN zip END)) AS work_zip
GROUP BY customer_id;
In the new code, the query will perform a single set of operations against the table once.
#pragprowrimo word count: 18,002