Date: Thu, 16 Feb 2017 15:19:57 -0700 (MST)
From: sqlQuestions <ryanpgodfrey@gmail.com>
To: pgsql-sql@postgresql.org
Message-ID: <1487283597419-5944796.post@n3.nabble.com>
Subject: Conditional Lookup Table with Like
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

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.category
	ELSE 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.


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