CODEWITHSUNDEEP
mysqlsqlsearch
Tyler Hughes
Tyler Hughes

Reputation: 602

Searching Through Multiple Tables

I have these 4 tables:

contacts_address (id, owner_id, address)  
contacts (id, name)  
contacts_groups (id, name)  
contacts_groups_link (contact_id, group_id)

Each contact has its own row in contacts. Each contact can have unlimited address attached to it, therefore contacts_address is an address attached to a contact by the 'owner_id' field. The user can also have groups. Each group has a row in contacts_groups. When you add a contact to a group it creates an entry in contacts_groups_link with that contacts id and that groups id.

I need a query statement where it will search through all contacts in a specific group by name and return those contacts.

I also need a query statement where it will search through all contacts and their attached address in a specific group and return those contacts.

I am so extremely confused on how that would work.

Here is the query I use to search through contacts and be able to also search through their addresses:

SELECT `contacts_address`.*, `contacts`.*
FROM `contacts` JOIN `contacts_address` ON `contacts_address`.`parent`=`contacts`.`id`
WHERE `contacts`.`owner`='$user_id'

Here is the query I use to grab all contacts within a specific group:

SELECT * FROM contacts INNER 
JOIN contacts_groups_link ON contacts_groups_link.contact_id = contacts.id 
WHERE contacts_groups_link.group_id='$id'

I'm not sure if this would help anyone. I'm so confused on how to combine them.

Upvotes: 0

Views: 91

Answers (1)

peterm
peterm

Reputation: 92785

Are you looking for something like this?

...search through all contacts in a specific group by name and return those contacts

SELECT c.id, c.name
  FROM contacts c JOIN contacts_groups_link l
    ON c.id = l.contact_id JOIN contacts_groups g
    ON l.group_id = g.id
 WHERE g.id = 1 -- << change to appropriate group id
   AND c.name LIKE '%Jhon%' -- << change to other pattern as needed

UPDATED: ...search through all contacts and their attached address and return those contacts 

SELECT c.id, c.name
  FROM contacts c JOIN contacts_groups_link l
    ON c.id = l.contact_id JOIN contacts_groups g
    ON l.group_id = g.id JOIN contacts_address a
    ON c.id = a.owner_id
 WHERE g.id = 1 -- << change to appropriate group id
   AND a.address LIKE '%Main St.%' -- << change to other pattern as needed
 GROUP BY c.id, c.name

Upvotes: 2

Related Questions