Thread: [SQL] Conditional Lookup Table with Like
I'm having trouble with a weird query. *table1* code description category *table2* code lookup_value category I want to pull the code, description and category from table1. Some, not all, codes from table1 have entries in table2, and some have multiple. In table2, where codes are equal, and when the lookup_value string is found inside the description, I want to pull the category from table2 instead of the category from table1. I was thinking about a case statement, but can't figure out the syntax. I appreciate any ideas that would help me out. Thanks a lot! SELECT table1.code, table1.description, CASE WHEN EXISTS( SELECT 1 FROM table1, table2 WHERE table1.code = table2.code AND table1.description LIKE '%table2.lookup_value%' LIMIT 1)THEN table2.categoryELSE table1.category END AS category FROM table1 -- View this message in context: http://postgresql.nabble.com/Conditional-Lookup-Table-with-Like-tp5944796.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
I'm having trouble with a weird query.
*table1*
code
description
category
*table2*
code
lookup_value
category
I want to pull the code, description and category from table1. Some, not
all, codes from table1 have entries in table2, and some have multiple. In
table2, where codes are equal, and when the lookup_value string is found
inside the description, I want to pull the category from table2 instead of
the category from table1.
I was thinking about a case statement, but can't figure out the syntax. I
appreciate any ideas that would help me out. Thanks a lot!
SELECT
table1.code,
table1.description,
CASE WHEN EXISTS
(
SELECT 1
FROM table1, table2
The reference to table1 in the from clause here seems wrong - usually you'd use the outer queries' table1 reference as part of a correlated subquery.
WHERE table1.code = table2.code
AND table1.description LIKE '%table2.lookup_value%'
LIMIT 1
In a correlated subquery within an EXISTS the LIMIT 1 is superfluous
)
THEN table2.category
ELSE table1.category
END AS category
FROM table1
In any case the subquery seems unnecessary..
SELECT code, description,
CASE WHEN table2.category IS NULL
THEN table1.category
WHEN description LIKE ('%' || lookup_value || '%')
THEN table2.category
ELSE table1.category
END
FROM table1
LEFT JOIN table2 USING (code);
David J.
Thanks for your response David.
I took out the WHEN table2.category IS NULL clause because I only want it to
pull the category from table2 when the lookup_value exists in description,
otherwise pull the table1 category. NULL doesn't matter to me.
Unfortunately, it still always pulls the category from table1.
SELECT code, description,
CASE WHEN description LIKE ('%' || lookup_value || '%') THEN table2.category ELSE table1.category
END
FROM table1
LEFT JOIN table2 USING (code);
--
View this message in context: http://postgresql.nabble.com/Conditional-Lookup-Table-with-Like-tp5944796p5944801.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
Thanks for your response David.
I took out the WHEN table2.category IS NULL clause because I only want it to
pull the category from table2 when the lookup_value exists in description,
otherwise pull the table1 category. NULL doesn't matter to me.
Unfortunately, it still always pulls the category from table1.
SELECT code, description,
CASE WHEN description LIKE ('%' || lookup_value || '%')
THEN table2.category
ELSE table1.category
END
FROM table1
LEFT JOIN table2 USING (code);
If you provide a self-contained example with records you expect and don't expect to match better help can be given.
David J.
OK, it's very close I think. I created a Fiddle that more closely represents what I need with an added table. http://sqlfiddle.com/#!15/5d76f/6 It's showing a few too many records and GROUP BY doesn't seem to help. Instead of 10, it should show these 6. product_code date_signed description category 1 February, 01 2017 00:00:00 i have a green truck vehicle 1 February, 01 2017 00:00:00 i have a RED car color 2 February, 01 2017 00:00:00 i have a blue boat vehicle 2 February, 01 2017 00:00:00 i have a blue rug vehicle 3 February, 01 2017 00:00:00 i have a dark cat (null) 3 February, 01 2017 00:00:00 i have a dark dog (null) -- View this message in context: http://postgresql.nabble.com/Conditional-Lookup-Table-with-Like-tp5944796p5944807.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
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?
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
WHERE date_signed = '2017-02-01';
create table table1 (
product_code int,
date_signed timestamp,
description varchar(20)
);
insert into table1
(product_code, date_signed, description)
values
(1, '2017-02-01', 'i have a RED car'),
(2, '2017-02-01', 'i have a blue boat'),
(3, '2017-02-01', 'i have a dark cat'),
(1, '2017-02-01', 'i have a green truck'),
(2, '2017-02-01', 'i have a blue rug'),
(3, '2017-02-01', 'i have a dark dog'),
(1, '2017-02-02', 'i REd NO SHOW'),
(2, '2017-02-02', 'i blue NO SHOW'),
(3, '2017-02-02', 'i dark NO SHOW');
create table table2 (
psc_code int,
category varchar(20)
);
insert into table2
(psc_code, category)
values
(1, 'vehicle'),
(2, 'vehicle');
create table table3 (
psc_code int,
lookup_value varchar(20),
category varchar(20)
);
insert into table3
(psc_code, lookup_value, category)
values
(1, 'fox', 'animal'),
(1, 'red', 'color'),
(1, 'box', 'shipping'),
(2, 'cat', 'animal');
--
View this message in context: http://postgresql.nabble.com/Conditional-Lookup-Table-with-Like-tp5944796p5944908.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
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.
That's pretty cool, I didn't know that one. But I still want to return the
result from table1 when there is null category.
This returns 4 correct results but needs to return the two product_code 3s
with null category:
SELECT table1.product_code, table1.date_signed, table1.description,
COALESCE (( SELECT table3.category FROM table3 WHERE table3.psc_code = table1.product_code AND
lower(table1.description)LIKE ('%' || lower(table3.lookup_value) ||
'%') ), table2.category)
FROM table1 JOIN table2 ON table2.psc_code = table1.product_code
WHERE date_signed = '2017-02-01';
--
View this message in context: http://postgresql.nabble.com/Conditional-Lookup-Table-with-Like-tp5944796p5944935.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
Ahh missing the LEFT JOIN. That's it! Thanks very much for your help David!
SELECT table1.product_code, table1.date_signed, table1.description,
COALESCE (( SELECT table3.category FROM table3 WHERE table3.psc_code = table1.product_code AND
lower(table1.description)LIKE ('%' || lower(table3.lookup_value) ||
'%') ), table2.category)
FROM table1 LEFT JOIN table2 ON table2.psc_code = table1.product_code
WHERE date_signed = '2017-02-01';
--
View this message in context: http://postgresql.nabble.com/Conditional-Lookup-Table-with-Like-tp5944796p5944937.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.