CODEWITHSUNDEEP
mysql
Sideshow
Sideshow

Reputation: 1361

MYSQL join and group by

I am having trouble getting the syntax right with a table join in MYSQL. I have two tables as below...

primary_table

agent_id | primary_area | key
-----------------------------
   10    |xx1           | 1  
   11    |xx2           | 2  
   12    |xx3           | 3  
etc...


secondary_table

agent_id | sec_area     | key
-----------------------------
   34    |xx1           | 5  
   37    |xx1           | 6  
   38    |xx7           | 8  
etc...

All I am trying to do is search (for example) both tables for the agent_id that has xx1 as a value in both the primary_area and 'sec_area'. I need to use LIKE as this is a search function and it is possible that a user would want to search for all that have the vale xx.

The returned data then needs to be grouped by agent_id and kept in an array.

Upvotes: 0

Views: 87

Answers (1)

fedorqui
fedorqui

Reputation: 290455

What about this?

SELECT agent_id FROM primary_table WHERE primary_area LIKE "%1"
UNION
SELECT agent_id FROM secondary_table WHERE sec_area LIKE "%1"

Comment: how would I group the results by agent_id using UNION

SELECT
  p.id
FROM
  primary_table AS p
JOIN
  sec_table AS s
ON
  p.id=s.id 
WHERE
  p.area LIKE "%1" AND s.area LIKE "%1"

SQL Fiddle

Upvotes: 1

Related Questions