CODEWITHSUNDEEP
sqljoininner-join
Mark Gerryl Mirandilla
Mark Gerryl Mirandilla

Reputation: 823

select data if exist either on both table or one of the tables

Im working on search using php and SQL. What I'm trying to achieve is get the data if it exist in any of the two tables or both.

This is my sql.

SELECT * FROM history_search 
INNER JOIN history_subs ON history_search.keyword = history_subs.keyword 
WHERE history_search.keyword LIKE '%$keyword%' OR history_subs.keyword LIKE '%$keyword%`'

history_subs

id, keyword, illegal, whitelist (1, Spotify.com, (Artists, api, community, investors, support, labs), open)

history_search

id, keyword, result, blacklist (1, meristone.com, 20, 0), (2, spotify.com, 19, 2),(3, cjrtec.com, 10, 1)

My problem is, it wont display data if keyword dont exist on both tables.

Hope you help me.

Upvotes: 0

Views: 1810

Answers (2)

Ali Hallaji
Ali Hallaji

Reputation: 4392

In your case, it would be better to use union

SELECT * FROM history_search 
right JOIN history_subs ON history_search.keyword = history_subs.keyword 
WHERE history_search.keyword LIKE '%$keyword%' OR history_subs.keyword LIKE 
'%$keyword%'
union 
SELECT * FROM history_search 
left JOIN history_subs ON history_search.keyword = history_subs.keyword 
WHERE history_search.keyword LIKE '%$keyword%' OR history_subs.keyword LIKE '%$keyword%'

Upvotes: 1

Derviş Kayımbaşıoğlu
Derviş Kayımbaşıoğlu

Reputation: 30565

You need to use full outer join if your criteria is to show result if it exists in any of the tables.

furthermore, If you use where condition on full outer join, your query will be converted to inner join implicitly

so the final query,

EDIT: Apparently Full Outer Join is not supported in MySql. Here is the alternative approach to Full Outer Join 

SELECT 
  * 
FROM history_search 
LEFT JOIN history_subs 
  ON history_search.keyword = history_subs.keyword AND history_search.keyword LIKE '%$keyword%'
UNION
SELECT 
  *
FROM history_subs
LEFT JOIN history_search
  ON history_search.keyword = history_subs.keyword AND history_subs.keyword LIKE '%$keyword%`'

Upvotes: 0

Related Questions