How to apply searching on two table using mysql

Question

Hi i have two table one is parentlist and childlist.

I have have to apply searching on this table by parentname and childname. I have provided my table structure for better understanding, I have to apply searching on two different field of with different field name.

Fieldname is name in parentlist table and childname in childlist table.

I want below output if I type va then parentlist and childlist record should come in that query like below example. With this va Srting i have a parentname varu123 and childname varu123 so I want these two record after executing the query.

This is the name of First table with fieldname

parentlist

............................................................
 id      name      mobilenumber  user_jid        email
............................................................
 1      varu123     123456         abc21        [email protected]

 2      abhishesk   123456         abc21        [email protected]

 3      harsh        1234          def22        [email protected]

This is the name of Second table with fieldname

 childlist

..........................................
id user_id    childname     Shoolname  
...........................................
 1    1        ram            St.paul
 2    1        raj            St.xavier
 3    2        varu123        St.paul
 4    2        arun           St.xavier
 5    3        kapil          St.paul
 6    3        kamal          St.xavier

I want this output: .

........................................................................................................
     id      name      mobilenumber  user_jid        email            childname        Shoolname
    ..........................................................................................................
     1      varu123     123456         abc21        [email protected]       ram,raj         St.paul,St.xavier

     2      abhishesk   123456        abc21        [email protected]        varu123,arun    St.paul,St.xavier

And what have you tried so far? SO is not a free coding service — RiggsFolly
– RiggsFolly, Commented Oct 23, 2015 at 9:47

Firas Rassas · Accepted Answer · 2015-10-23 09:45:43Z

1

select pl.*, GROUP_CONCAT(cl.childname), GROUP_CONCAT(cl.Shoolname)
from parentlist as pl
inner join childlist as cl on pl.id=cl.user_id
where pl.name like '%va%' or cl.childname like '%va%'

answered Oct 23, 2015 at 9:45

Firas Rassas

5094 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

varun joshi Over a year ago

This query only gives me the record of abhishek if i type varu123 i want the record of both varu123 and abhishek.one is a parentname and another is a childname.

Abhishek Sharma · Accepted Answer · 2015-10-23 09:46:52Z

0

use MySQL inner join or where :-

select * from parentlist inner join 
childlist on parentlist.id=childlist.user_id 
where childname='varu123' or parentlist.name='varu123'

use MySQL inner join or like :-

select * from parentlist inner join 
childlist on parentlist.id=childlist.user_id 
where childname like '%varu123%' or parentlist.name like '%varu123%'

edited Oct 23, 2015 at 9:46

answered Oct 23, 2015 at 9:42

Abhishek Sharma

6,6241 gold badge17 silver badges20 bronze badges

7 Comments

varun joshi Over a year ago

This query is litile bit right but not the exact what i want it gives me abhishek 123456 abc21 varu123 varu123 123456 abc21 raj varu123 123456 abc21 ram For detail refer my o/p in this one childname is missing or parent abhishek in second record varu123 comes twice i want this abhishek 123456 abc21 varu123,arun varu123 123456 abc21 raj,ram

Abhishek Sharma Over a year ago

select *,GROUP_CONCAT(childname), GROUP_CONCAT(Shoolname) from parentlist inner join childlist on parentlist.id=childlist.user_id where childname like '%varu123%' or parentlist.name like '%varu123%' group by user_id

Abhishek Sharma Over a year ago

try this query @varunjoshi

varun joshi Over a year ago

Hi rahautos, In this query it is not giving me the second childname of abhishek which is arun it gives me only varu123 and i want varu123,arun otherwise the query was right .

Abhishek Sharma Over a year ago

select *,GROUP_CONCAT(childname), GROUP_CONCAT(Shoolname) from parentlist inner join childlist on parentlist.id=childlist.user_id where user_id in (select user_id from parentlist inner join childlist on parentlist.id=childlist.user_id where childname like '%varu123%' or parentlist.name like '%varu123%' group by user_id) group by user_id

|

Collectives™ on Stack Overflow

How to apply searching on two table using mysql

2 Answers 2

1 Comment

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related