CODEWITHSUNDEEP
mysql
verism
verism

Reputation: 1164

Perform inner join filtering defined values

Let's say I have the following two tables. What I'm trying to achieve is a search that returns ranked results, when a match is found in A1.pagetitle, A1.content or A2.value - in that order.

But I only need to include results from the second table where the tmplvarid matches specific values (in this case, 15 or 17).

 mod_site_content (A1)
 +----+-----------+--------------+
 | id | pagetitle | content      |
 +----+-----------+--------------+
 | 1  | home      | <p> ... </p> |
 | 2  | project 1 | <p> ... </p> |
 | 3  | comment 1 | <p> ... </p> |
 | 4  | project 2 | <p> ... </p> |
 | 5  | project 3 | <p> ... </p> |
 | 6  | comment 2 | <p> ... </p> |
 | etc ...                       |
 +----+-----------+--------------+

 mod_site_tmplvar_contentvalues (A2)
 +----+-----------+-----------+-----------------+
 | id | tmplvarid | contentid | value           |
 +----+-----------+-----------+-----------------+
 | 1  | 2         | 1         | [{"key":"val"}] |
 | 2  | 2         | 2         | [{"key":"val"}] |
 | 3  | 5         | 1         | [{"key":"val"}] |
 | 4  | 5         | 5         | [{"key":"val"}] |
 | 5  | 15        | 4         | [{"key":"val"}] |
 | 6  | 15        | 5         | [{"key":"val"}] |
 | etc ...                                      |
 +----+-----------+-----------+-----------------+

I think I'm almost there - currently I have:

SELECT DISTINCT A1.pagetitle, A1.content, A1.uri, A2.value,
    CASE
        WHEN A1.pagetitle LIKE ? THEN 1
        WHEN A1.content LIKE ? THEN 2
        WHEN A2.value LIKE ? THEN 3
    END AS rank
FROM mod_site_content A1
INNER JOIN mod_site_tmplvar_contentvalues A2
    ON A1.id = A2.contentid
WHERE pagetitle LIKE ?
    OR content LIKE ?
    OR value LIKE ?
ORDER BY rank

But this includes all corresponding contentids - I can't for the life of me figure out how to incorporate a condition that only includes certain ones.

Upvotes: 1

Views: 39

Answers (2)

John Ruddell
John Ruddell

Reputation: 25862

when you join filter the results on the join column like so

FOR RETURNING ROWS WHEN IN BOTH TABLES:

SELECT DISTINCT A1.pagetitle, A1.content, A1.uri, A2.value,
    CASE
        WHEN A1.pagetitle LIKE ? THEN 1
        WHEN A1.content LIKE ? THEN 2
        WHEN A2.value LIKE ? THEN 3
    END AS rank
FROM mod_site_content A1
INNER JOIN mod_site_tmplvar_contentvalues A2
    ON A1.id = A2.contentid
    AND A2.tmplvarid IN(15,17)
WHERE (pagetitle LIKE ?
    OR content LIKE ?
    OR value LIKE ?)

DEMO

FOR RETURNING ROWS FROM mod_site_content AND ANY MATCHING FROM mod_site_tmplvar_contentvalues

SELECT DISTINCT A1.pagetitle, A1.content, A1.uri, A2.value,
    CASE
        WHEN A1.pagetitle LIKE ? THEN 1
        WHEN A1.content LIKE ? THEN 2
        WHEN A2.value LIKE ? THEN 3
    END AS rank
FROM mod_site_content A1
LEFT JOIN mod_site_tmplvar_contentvalues A2
    ON A1.id = A2.contentid
    AND A2.tmplvarid IN(15,17)
WHERE (pagetitle LIKE ?
    OR content LIKE ?
    OR value LIKE ?)

DEMO

Upvotes: 2

Dany Balian
Dany Balian

Reputation: 608

just add your condition at the end!

SELECT DISTINCT A1.pagetitle, A1.content, A1.uri, A2.value,
    CASE
        WHEN A1.pagetitle LIKE ? THEN 1
        WHEN A1.content LIKE ? THEN 2
        WHEN A2.value LIKE ? THEN 3
    END AS rank
FROM mod_site_content A1
INNER JOIN mod_site_tmplvar_contentvalues A2
    ON A1.id = A2.contentid
WHERE (pagetitle LIKE ?
    OR content LIKE ?
    OR value LIKE ?)
    AND A2.contentid in (15,17)

ORDER BY rank

Upvotes: 1

Related Questions