Mysql query for searching keyword

Question

I have a search keyword and I am searching for that keyword using two tables.

The search keyword is 'Patient'

Table - default_pages

id        type_id     parent_id         status
68        16          0                 draft
70        17          68                live
227       17          44                live
262       1           31                live

Table - default_search

id       title                 entry_id
1        patient status        70
2        patient check         227
3        patient health        262

My query is

"SELECT 
            s.title, p.id, p.type_id AS sqem 

            FROM default_search s LEFT JOIN default_pages p ON p.id=s.entry_id  

            WHERE s.title LIKE '%Patient%' 

            HAVING sqem IS NOT NULL"

The above query returns 3 results where the ids from default pages are 70, 227, 262 but the problem is for the id 70, it's parent id is 68 and the status of id 68 is draft so I want to exclude this row from the result set and this is where I am stuck.

Any help is highly appreciated. Thanks in advance.

score 0 · Answer 1 · answered Dec 01 '14 at 22:41

0

Check for status in your default_pages table:

SELECT 
s.title, p.id, p.type_id AS sqem 
FROM default_search s LEFT JOIN default_pages p ON p.id=s.entry_id  
WHERE s.title LIKE '%Patient%' AND p.status='live'
HAVING sqem IS NOT NULL

answered Dec 01 '14 at 22:41

RNK

5,582
11
65
133

It returns the same 3 rows. But I don't want 70 to be included in the results because it's parent has got 'draft' status. – Raj Dec 01 '14 at 22:45
I think you should look into this: http://stackoverflow.com/a/1446831/3067928 and modify your query according to that. Why don't you create sqlfiddle. So, other can also try on it. – RNK Dec 01 '14 at 22:49

score 0 · Answer 2 · answered Dec 02 '14 at 00:07

SELECT 
    s.title, p.id, p.type_id AS sqem 
    FROM default_search s LEFT JOIN default_pages p ON p.id=s.entry_id
    LEFT JOIN default_pages dp ON p.parent_id = dp.id
    WHERE s.title LIKE '%Patient%' AND p.status='live' AND dp.status='live'
    HAVING sqem IS NOT NULL

Another Left join with default_pages "connecting" parent_id with id ?

Mysql query for searching keyword

2 Answers2