agora inbox for pgsql-sql@postgresql.org  

help / color / mirror / Atom feed

From: sqlQuestions <ryanpgodfrey@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Re: Conditional Lookup Table with Like
Date: Fri, 17 Feb 2017 08:33:37 -0700 (MST)
Message-ID: <1487345617421-5944908.post@n3.nabble.com> (raw)
In-Reply-To: <CAKFQuwbdNiN=-ij7Lz0gc6XRwe2cPoVKyc+WH-Z_9+7FR=yj5g@mail.gmail.com>
References: <1487283597419-5944796.post@n3.nabble.com>
	<CAKFQuwYpF146ygOUSXSsAxdAKGsSUXt=a5HtRpUQT=YTy+mwkw@mail.gmail.com>
	<1487286348488-5944801.post@n3.nabble.com>
	<CAKFQuwbdNiN=-ij7Lz0gc6XRwe2cPoVKyc+WH-Z_9+7FR=yj5g@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>

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.


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

view thread (9+ messages)  latest in thread

Message-ID: <1487345617421-5944908.post@n3.nabble.com>
Permalink:  ../1487345617421-5944908.post@n3.nabble.com/
Also on:    postgresql.org/message-id/1487345617421-5944908.post@n3.nabble.com

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: ryanpgodfrey@gmail.com
  Subject: Re: Conditional Lookup Table with Like
  In-Reply-To: <1487345617421-5944908.post@n3.nabble.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox