CODEWITHSUNDEEP
mysqlsqljoin
aur0n
aur0n

Reputation: 473

Search in joined table with multiple records

I have a table 'contacts' which is structured like this:

And contacts_related

One contact can have multiple related contacts (joined by contact_id). What I would like to do is make a SELECT query on contacts that if a search input is given (example: 'John') it prints all contacts that have John in name OR contacts that have a related contact with 'John' in the name.

Any suggestions on how to do this, without making two distinct queries?

Upvotes: 1

Views: 89

Answers (4)

Barry
Barry

Reputation: 3723

A possible solution is

SELECT 
    DISTINCT C.*
FROM 
    contacts C LEFT JOIN contacts_related R
    ON C.id=R.contact_id
WHERE
    C.name LIKE '%John%' OR 
    R.name LIKE '%John%'

Upvotes: 1

jawadxiv
jawadxiv

Reputation: 59

Below is your answer, i have tested and its working!!!

SELECT c.name 
FROM contact T
    INNER JOIN contact_r C ON C.Id = T.id
WHERE C.name LIKE '%saif%'

data in contact Table enter image description here

data in contact_r Table: enter image description here

Upvotes: 0

Kishore
Kishore

Reputation: 5891

try this,

select a.id,a.name,a.surname from 
contacts a join contacts_related b 
on SOUNDEX(a.name)=SOUNDEX(a.name)

or

select a.id,a.name,a.surname from 
contacts a, contacts_related b 
where SOUNDEX(a.name)=SOUNDEX(b.name)

select column according to your need.

Upvotes: 0

Thorsten Kettner
Thorsten Kettner

Reputation: 94914

You want those contacts that are either Johns themselves or have a John relation. To look up the relations use EXISTS or IN.

select * 
from contacts
where name like '%John%'
or exists
(
  select *
  from contacts_related
  where name like '%John%'
  and contact_id = contacts.id
);

Or:

select * 
from contacts
where name like '%John%'
or id in
(
  select contact_id
  from contacts_related
  where name like '%John%'
);

Upvotes: 0

Related Questions