CODEWITHSUNDEEP
mysqlsql
user1956388
user1956388

Reputation: 1

How to put together two queries of same table

I have trouble with joining two query into one.

query 1:

SELECT *
FROM (`text`)
WHERE `text` LIKE '%TERM1%' ORDER BY `text`.`start` DESC

query 2:

SELECT *,
  MATCH (col1,  col2,  col3) AGAINST ('%TERM2%' IN BOOLEAN MODE) AS relevance
FROM `text`
WHERE MATCH (col1, col2, col3) AGAINST ('%TERM2%' IN BOOLEAN MODE)
      AND status=2
ORDER BY `text`.`start` DESC

any suggestion would be accepted.

Table 'text' look like

+-------+--------+-------+------+------+
|  col1 |  col2  | col3  | text |  id  |
+-------+--------+-------+------+------+
| name1 | place1 | date1 | val1 |   1  |
| name2 | place2 | date2 | val2 |   2  |
| name3 | place2 | date5 | val1 |   3  |
| name4 | place4 | date4 | val5 |   4  |
| name5 | place5 | date5 | val5 |   5  |
:
:
| namex | placex | datex | valx |   x  |
+-------+--------+-------+------+------+

I have forgot to write, first query also must be ordered by start DESC...

I expect result like: term1 = val1, and term2 = val5

+-------+--------+-------+------+------+
|  col1 |  col2  | col3  | text |  id  |
+-------+--------+-------+------+------+

qery1 result:

| name3 | place2 | date5 | val1 |   3  |
| name1 | place1 | date1 | val1 |   1  |

query2 result

| name5 | place5 | date5 | val5 |   5  |
| name4 | place4 | date4 | val5 |   4  |
:
:
| namex | placex | datex | valx |   1  |
+-------+--------+-------+------+------+

Thanks.

@PinnyM - Yes table are named text and inside table stand column text, unfortu, I have adopt project with complete database, and it was absurdly, but also it is absurdly go to change complete code because it is necessary or not change of table name.

Upvotes: 0

Views: 188

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269843

This is one way:

SELECT t.*, -1 as relevance
FROM `text` t
WHERE `text` LIKE '%TERM1%'
union all
SELECT t.*, MATCH (col1,  col2,  col3) AGAINST ('%TERM2%' IN BOOLEAN MODE) AS relevance
FROM `text` t
WHERE MATCH (col1, col2, col3) AGAINST ('%TERM2%' IN BOOLEAN MODE) AND status=2
ORDER BY `text`.`start` DESC

Upvotes: 1

Related Questions