CODEWITHSUNDEEP
mysql
KingRichard
KingRichard

Reputation: 1244

Can I select a value from different columns as one value in MySQL?

I'm not sure if I've phrased this question correctly, but I've never had a need to do something like this before.

I have 2 tables: articles and article_content that I want to search for a keyword.

Both tables have correlating columns, ID and article_ID, respectively.

articles contains the titles, and article_content contains the body and other meta.

I'm trying to do a keyword search that searches the title and the body content, but since they're in two different tables, I'm not sure how to return the ID if either table has a match.

Is it possible to do something like this in MySQL? Here's kind of what I'm thinking, this is obviously not valid code:

SELECT `ID` FROM `articles` OR `article_ID` from `article_content`
WHERE
    EITHER
        `articles`.`title` LIKE '%SearchTerm%' OR
        `article_content`.`description` LIKE '%SearchTerm%'

I'm trying to run this as one query, because I have an ORDER BY CASE... that weights the results.

I've been Googling, but I don't know what this would even be called so I'm stuck. Any help is appreciated!

Upvotes: 0

Views: 29

Answers (2)

Gautham Krishnan
Gautham Krishnan

Reputation: 36

SELECT a.id
FROM   articles a,
       article_content ac
WHERE  a.title LIKE '%SearchTerm%'
        OR ac.description LIKE '%SearchTerm%';

You don't need to select both articles.ID and article_content. article_ID as both of them will be the same.

Upvotes: 1

Akina
Akina

Reputation: 42728

SELECT 'article' source, `ID` 
FROM `articles` 
WHERE `title` LIKE '%SearchTerm%'
UNION ALL
SELECT 'content', `article_ID` 
FROM `article_content`
WHERE `description` LIKE '%SearchTerm%'

If you are not interested in the info where the term is found then

SELECT `ID` 
FROM `articles` 
WHERE `title` LIKE '%SearchTerm%'
UNION DISTINCT
SELECT `article_ID` 
FROM `article_content`
WHERE `description` LIKE '%SearchTerm%'

Upvotes: 1

Related Questions