CODEWITHSUNDEEP
mysqlsql
Dmitry S.
Dmitry S.

Reputation: 9

Mysql How to select parent rows

Sample Data:

id parent text1 text2 ... textN
1 0 text1 text2 ... ...
2 1 text3 sdfsdf_text4 ... ...
3 1 text5 text4 ... ...
4 0 text1 text2 ... ...
5 4 text3 adsfads_text4 text5 ...

I need a query to find text using LIKE '%text4%' and get only parent = 0 rows.

Expected Output:

id parent text1 text2 ... textN
1 0 text1 text2 ... ...
4 0 text1 text2 ... ...

I'm trying

SELECT * FROM `table` AS P /*parents*/
INNER JOIN `table` AS C /*childs*/
ON (C.id = P.id) 

WHERE ( 
    
    (
        ((P.`text1` LIKE '%text4%')OR (P.`text2` LIKE '%text4%')OR (P.`text3` LIKE '%text4%')OR (P.`text4` LIKE '%text4%'))
    )
        OR (
            (C.`parent`>0) AND (
                ((C.`text1` LIKE '%text4%')OR (C.`text2` LIKE '%text4%')OR (C.`text3` LIKE '%text4%')OR (C.`text4` LIKE '%text4%'))
            )
                                
        )
    ) 

GROUP BY CASE WHEN C.`parent`>0 THEN C.`parent` ELSE P.`id` END

have result - rows ids 2,3,5

how to get parent rows (e.g. where parent = 0), when parent or childs has 'text4'

my table result's screen

I need add row id=6525, and no id=6654
row 6525 don't contain 'text4', but childs have

Upvotes: 0

Views: 144

Answers (1)

JohnH
JohnH

Reputation: 2143

How about ...

SELECT * FROM  <table> WHERE text1 LIKE '%text4%' AND parent = 0

... or, to include a search of the child table ...

SELECT P.* FROM <ParentTable> AS P
INNER JOIN <ChildTable> AS C ON 
(C.id = P.id) AND (C.parent = 0)

Upvotes: 0

Related Questions