Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Need MySQL query Help.

Please find schema below:

Table1
PK_id    Table1_Title    Table1_Description
1        Test1           This is Test1
2        Test2           This is Test2

Table2
FK_id    Table2_Title    Table2_Description
1        Test2           This is Test2  
1        Test3           This is Test3

Here "Table2" is in child relationship with Master table "Table1". I want to search in Title and Description columns of above 2 tables. If search string is found in master table "Table1" then that row along with all the rows of child should be returned. Simillarly if search string is found in one of the child tabel row then the coresponding Master table row and all its child should be returned. To clarify please find result below:

Result:

Case 1:
If searching TEST2 Then

PK_id    Table1_Title    Table1_Description    FK_id    Table2_Title    Table2_Description
1        Test1           This is Test1         1        Test2           This is Test2
1        Test1           This is Test1         1        Test3           This is Test3
2        Test2           This is Test2         NULL     NULL            NULL


Case 2:
If searching TEST1 Then

PK_id    Table1_Title    Table1_Description    FK_id    Table2_Title    Table2_Description
1        Test1           This is Test1         1        Test2           This is Test2
1        Test1           This is Test1         1        Test3           This is Test3


Case 3:
If searching TEST3 Then

PK_id    Table1_Title    Table1_Description    FK_id    Table2_Title    Table2_Description
1        Test1           This is Test1         1        Test2           This is Test2
1        Test1           This is Test1         1        Test3           This is Test3

Is it possible to have a result with single query or something else?

Please help.

share|improve this question
add comment

1 Answer 1

up vote 0 down vote accepted
SELECT  t1.*, 
        t2.*

FROM    Table1 AS t1
    LEFT JOIN
        Table2 AS t2
            ON WHERE t2s.FK_id = t1.PK_id

WHERE   t1.Table1_Title = 'search text'
   OR   EXISTS
        ( SELECT  *
          FROM    Table2 AS t2s
          WHERE   t2s.FK_id = t1.PK_id
            AND   t2s.Table2_Title = 'search text'
        ) ;
share|improve this answer
    
Hello ypercube, Thanks for solution. It worked. –  Asif Mulla Feb 16 '13 at 9:12
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.