CODEWITHSUNDEEP
phpmysql
varun joshi
varun joshi

Reputation: 481

How to apply searching on two table using mysql

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

Upvotes: 3

Views: 66

Answers (2)

Abhishek Sharma
Abhishek Sharma

Reputation: 6661

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%'

Upvotes: 0

Firas Rassas
Firas Rassas

Reputation: 509

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%'

Upvotes: 1

Related Questions