CODEWITHSUNDEEP
mysqlsql
Djo64
Djo64

Reputation: 11

How to search column that is not in the same table?

This amazing query works well and I am so happy:

SELECT
post_id,
username,
post_title,
description,
post_tags,
post_cats,
MATCH(username) AGAINST (+(:keyword) IN BOOLEAN MODE)  AS  score_username,
MATCH(post_title) AGAINST (+(:keyword) IN BOOLEAN MODE)  AS  score_title,
MATCH(description) AGAINST (+(:keyword) IN BOOLEAN MODE) AS score_description,
MATCH(post_tags) AGAINST (+(:keyword) IN BOOLEAN MODE) AS score_tags,
MATCH(post_cats) AGAINST (+(:keyword) IN BOOLEAN MODE) AS score_cats
FROM topics
WHERE
MATCH(username) AGAINST (+(:keyword) IN BOOLEAN MODE) OR
MATCH(post_title) AGAINST (+(:keyword) IN BOOLEAN MODE) OR
MATCH(description) AGAINST (+(:keyword) IN BOOLEAN MODE)OR
MATCH(post_tags) AGAINST (+(:keyword) IN BOOLEAN MODE)OR
MATCH(post_cats) AGAINST (+(:keyword) IN BOOLEAN MODE)
ORDER BY is_online DESC, (score_username+score_title*0.5+score_description*0.4+score_tags*0.3+score_cats*0.2) DESC

But now the "descriptions" are in another table name "topics2" with the same "post_id" and one column per language:

"description_en", "description_fr", "description_it".

How could I select, with the same query, this other table, and then make it work with all language description ?

Upvotes: 0

Views: 77

Answers (2)

Luis Blanche
Luis Blanche

Reputation: 627

You will need to use JOINS. Follow this Tutorial on joins in MYSQL

SELECT TABLE_A.COLUMN_X., TABLE_B.COLUMN_Y FROM TABLE_A LEFT JOIN TABLE_B ON TABLE_A.ID=TABLE_B.ID

Giving you : 

SELECT 
topics1.post_id,
topics1.username,
topics1.post_title,
topics1.description,
topics1.post_tags,
topics1.post_cats,
topics2.description_en, 
topics2.description_fr,
topics2.description_it 
FROM 
topics2 INNER JOIN 
(SELECT
post_id,
username,
post_title,
description,
post_tags,
post_cats,
MATCH(username) AGAINST (+(:keyword) IN BOOLEAN MODE)  AS  score_username,
MATCH(post_title) AGAINST (+(:keyword) IN BOOLEAN MODE)  AS  score_title,
MATCH(description) AGAINST (+(:keyword) IN BOOLEAN MODE) AS score_description,
MATCH(post_tags) AGAINST (+(:keyword) IN BOOLEAN MODE) AS score_tags,
MATCH(post_cats) AGAINST (+(:keyword) IN BOOLEAN MODE) AS score_cats
FROM topics
WHERE
MATCH(username) AGAINST (+(:keyword) IN BOOLEAN MODE) OR
MATCH(post_title) AGAINST (+(:keyword) IN BOOLEAN MODE) OR
MATCH(description) AGAINST (+(:keyword) IN BOOLEAN MODE)OR
MATCH(post_tags) AGAINST (+(:keyword) IN BOOLEAN MODE)OR
MATCH(post_cats) AGAINST (+(:keyword) IN BOOLEAN MODE)
ORDER BY is_online DESC, (score_username+score_title*0.5+score_description*0.4+score_tags*0.3+score_cats*0.2) DESC) topics1 

ON topics2.post_id=topics1.post_id

Upvotes: 2

RDFozz
RDFozz

Reputation: 215

Here's your query with the JOIN added. I just included one of the description columns; you of course need to figureo ut which you need, and so forth.

Note: code untested.

SELECT
       post_id,
       username,
       post_title,
       description,
       post_tags,
       post_cats,
       MATCH(username) AGAINST (+(:keyword) IN BOOLEAN MODE)  AS  score_username,
       MATCH(post_title) AGAINST (+(:keyword) IN BOOLEAN MODE)  AS  score_title,
       MATCH(description) AGAINST (+(:keyword) IN BOOLEAN MODE) AS score_description,
       MATCH(post_tags) AGAINST (+(:keyword) IN BOOLEAN MODE) AS score_tags,
       MATCH(post_cats) AGAINST (+(:keyword) IN BOOLEAN MODE) AS score_cats,
       t2.description_en

FROM topics
     INNER JOIN topics2 t2 ON (topics.post_id = t2.post_id)

WHERE
MATCH(username) AGAINST (+(:keyword) IN BOOLEAN MODE) OR
MATCH(post_title) AGAINST (+(:keyword) IN BOOLEAN MODE) OR
MATCH(description) AGAINST (+(:keyword) IN BOOLEAN MODE)OR
MATCH(post_tags) AGAINST (+(:keyword) IN BOOLEAN MODE)OR
MATCH(post_cats) AGAINST (+(:keyword) IN BOOLEAN MODE)
ORDER BY is_online DESC,
         (score_username+score_title*0.5+score_description*0.4+score_tags*0.3+score_cats*0.2) DESC

Just that one extra line in the FROM clause.

As you can see, the MATCHes don't impact the JOIN at all - you just need to specify how the two tables are related.

Upvotes: 1

Related Questions