how to do a select on like values from multiple columns in sql

Question

I have two tables with columns:

Genres  
  ID  
  genre

and

Adjectives
  ID
  adjective_title

I need to do a select that returns the matching values from both tables columns with the like syntax.

For example if ep was the value entered using like the results would look like:

result_column:
--------------
epiphonic -- (from genres table)
epic      -- (from adjectives table)

etc . . .

I'm pretty sure I need to use a subquery to return the results.

Johnno Nolan · Accepted Answer · 2009-10-12 14:11:50Z

8

try this

SELECT genre AS result
FROM genres
WHERE genre LIKE '%ep%'
UNION
SELECT adjective_title AS result
FROM 
  adjectives
WHERE adjective_title LIKE '%ep%'

The union will eliminate duplicates in each query use UNION ALL for each.

answered Oct 12, 2009 at 14:11

Johnno Nolan

29.7k19 gold badges114 silver badges160 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

rsp · Accepted Answer · 2009-10-12 14:18:30Z

0

You can do this without sub-selects, but it won't be quick on large tables, by using concat to build the string you use the like operator on:

select g.ID, g.genre, a.adjective_title from Genres g, Adjectives a where
concat(g.genre, a.adjective_title) like '%epic%'

answered Oct 12, 2009 at 14:18

rsp

23.4k6 gold badges59 silver badges72 bronze badges

Collectives™ on Stack Overflow

how to do a select on like values from multiple columns in sql

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related