CODEWITHSUNDEEP
mysqlunionunion-all
user998163
user998163

Reputation: 483

Search in two tables and order by occurrence of term

I have this query to search in two SQL tables. I am looking for a way to sort the result by occurrence. This is my query:

SELECT `parent_id`
FROM wp_forum_posts
WHERE text LIKE '%{$term1}%'
UNION
SELECT `id`
FROM wp_forum_threads
WHERE subject LIKE '%{$term1}%

Which is the best way, to get the results ordered?

Upvotes: 0

Views: 78

Answers (2)

Bohemian
Bohemian

Reputation: 425043

The trick is first to use UNION ALL, which preserves duplicates (ordinary UNION removes duplicates), then select from that result. This query should do it:

select * from (
    select parent_id as mID, count(*) as cnt
    from wp_forum_posts
    where text like '%{$term1}%'
    group by 1
  UNION ALL
    select id, count(*)
    FROM wp_forum_threads
    where subject like '%{$term1}%
    group by 1) x
order by 2, 1

Upvotes: 1

xQbert
xQbert

Reputation: 35333

Assumes ID and parent_ID are not duplicates in tables otherwise you can get 2 rows per an id... and would you want them summed together if so then are parent_ID and ID related?

Select mID, cnt
FROM 
(SELECT `parent_id` as mID, count(`parent_ID`) as cnt
FROM wp_forum_posts
WHERE text LIKE '%{$term1}%'
Group by `parent_ID`
UNION
SELECT `id`, count(`id`) as cnt
FROM wp_forum_threads
WHERE subject LIKE '%{$term1}%
GROUP BY `id`)
Order by cnt ASC, mID

Upvotes: 0

Related Questions