From: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
---|---|
To: | sqlQuestions <ryanpgodfrey(at)gmail(dot)com> |
Cc: | "pgsql-sql(at)postgresql(dot)org" <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: Conditional Lookup Table with Like |
Date: | 2017-02-17 16:41:57 |
Message-ID: | CAKFQuwZx7x7DijFfGzp26zW8PDnv9wrEz2JsS232yyB+VPZmvg@mail.gmail.com |
Views: | Whole Thread | Raw Message | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
On Fri, Feb 17, 2017 at 8:33 AM, sqlQuestions <ryanpgodfrey(at)gmail(dot)com>
wrote:
> Hi David,
>
> You got me really close, but I'm still not getting the correct results due
> to what I've learned is called cartesian product. The result set is being
> multiplied because of extra rows. Have you seen this before?
>
You added a third table ... the fact that my answer doesn't work isn't
surprising.
You might need to go back to using a correlated subquery:
SELECT ...
COALESCE ((SELECT ... FROM table3 WHERE table3 = table1[outer reference]),
table2.category)
FROM table1 JOIN table2
That, or modify the JOIN clause as noted below:
> Here is my query with an example schema. It should only return the first 6
> rows in table1 with whichever category is correct.
>
> SELECT table1.product_code, table1.date_signed, table1.description,
> CASE
> WHEN lower(table1.description) LIKE ('%' || lower(table3.lookup_value) ||
> '%')
> THEN table3.category
> ELSE table2.category
> END
> FROM table1
> LEFT JOIN table2 ON table2.psc_code = table1.product_code
>
> LEFT JOIN table3 ON table3.psc_code = table1.product_code
>
This join above must return zero or one records to avoid a duplicating
rows in table1. To do so a match on product_code/psc_code is
insufficient. You need to move the "table1.description LIKE [...]"
expression here. When the expression is false the row from table3 will be
all nulls. As shown above you can use COALESCE to pick the table3 value if
its non-null otherwise pick the table2 value.
I would highly suggest you define primary keys on your tables...
David J.
From | Date | Subject | |
---|---|---|---|
Next Message | sqlQuestions | 2017-02-17 17:56:01 | Re: Conditional Lookup Table with Like |
Previous Message | sqlQuestions | 2017-02-17 15:33:37 | Re: Conditional Lookup Table with Like |