compare values and partial names

Manuel Antunes

The SELECT statement would return the following:

c.first_name c.last_name c.credit_limit new_limit max(p.credit_limit)

Elizabeth Smith $10,000.00 10,000.


SELECT
  c.first_name,
  c.last_name,
  c.credit_limit AS "old_limit",
  max(p.credit_limit) AS "new_limit"
FROM customers c, prospects p
WHERE lower(p.full_name) LIKE '%' || lower(c.first_name) || '%'
AND lower(p.full_name) LIKE '%' || lower(c.last_name) || '%'
AND p.credit_limit > c.credit_limit
GROUP BY c.first_name, c.last_name, c.credit_limit
ORDER BY first_name;

-------------------------------------
-------------------------------------

CREATE INDEX ON customers (lower(first_name || ' ' || last_name), lower(first_name || ',' || last_name));
CREATE INDEX ON prospects (lower(full_name));

SELECT a.first_name,
        a.last_name,
        a.credit_limit AS old_limit,
        max(b.credit_limit) AS new_limit
FROM customers a JOIN prospects b
  ON lower(full_name) IN (
    lower(a.first_name || ' ' || a.last_name),
    lower(a.last_name || ', ' || a.first_name)
  )
GROUP BY a.id
  HAVING max(b.credit_limit) > a.credit_limit
ORDER BY first_name, last_name
--------------------
--------------------

create extension pg_trgm;
create index prospects_idx on prospects using gin(full_name gin_trgm_ops);


SELECT c.first_name
      , c.last_name
      , c.credit_limit as old_limit
      , MAX(p.credit_limit) as new_limit
FROM customers c JOIN prospects p
ON p.full_name ILIKE CONCAT('%', c.first_name, '%')
and p.full_name ILIKE CONCAT('%', c.last_name, '%')
and p.credit_limit > c.credit_limit
GROUP BY c.first_name, c.last_name, c.credit_limit
ORDER BY c.first_name