0

Following is my table for artists -

id  name    sex
1   harsh   male
2   geet    female

Following is my table for events -

id  artist_id  created_by
2   2          16
2   2          17

Following is my query -

SELECT * FROM `events` WHERE artist_id IN (SELECT id FROM `artists` WHERE name LIKE '%$search_term%')

But apart from events data I need to get artist name in the result as well, please let me know what I need to change in my query as I tried *, artists.name it wont worked.

Juru
  • 1,623
  • 17
  • 43
Trialcoder
  • 5,816
  • 9
  • 44
  • 66
  • possible duplicate of [Subqueries vs joins](http://stackoverflow.com/questions/141278/subqueries-vs-joins) – Juru Oct 06 '14 at 18:59

3 Answers3

1

Try this query

SELECT e.*
FROM `artists` AS a
JOIN `events` AS e ON e.artist_id = a.id
WHERE a.name LIKE '%$search_term%';
Jevgenijs Vaikulis
  • 676
  • 4
  • 7
1

You need to select from two tables simultaneously. Use the join for that 

SELECT artists.name, events.*
FROM artists
INNER JOIN events
    ON artist.id = artist_id
WHERE
    name LIKE '%search_term%'
Cthulhu
  • 1,379
  • 1
  • 13
  • 25
1

Use a join instead of an IN

SELECT 
  e.*, 
  artists.name
FROM 
  `events` e
  inner join `artists` a  on e.artist_id = a.id
WHERE 
  name LIKE '%$search_term%'
crthompson
  • 15,653
  • 6
  • 58
  • 80