Sedrick Jefferson Sedrick Jefferson - 1 month ago 8
MySQL Question

How to intersect multiple queries using MYSQL?

I am trying to intersect these queries. I am going to have from two intersecting queries to five intersecting queries.

select * from Main
left join title_keyword_link on Main.id = title_keyword_link.title_id
left join keywords_master on title_keyword_link.keyword_id = keywords_master.id
where keywords_master.keyword = 'opinion'

INTERSECT

select * from Main
left join title_keyword_link on Main.id = title_keyword_link.title_id
left join keywords_master on title_keyword_link.keyword_id = keywords_master.id
where keywords_master.keyword = 'sports'


I found out MYSQL does not support INTERSECT, so I tried some of the suggested alternatives. I tried:

select * from Main
left join title_keyword_link on Main.id = title_keyword_link.title_id
left join keywords_master on title_keyword_link.keyword_id = keywords_master.id
where keywords_master.keyword in ( 'opinion', 'sports')


^This shows results that look more like a UNION of opinion and sports

I also tried:

select keywords from Main
left join title_keyword_link on Main.id = title_keyword_link.title_id
left join keywords_master on title_keyword_link.keyword_id = keywords_master.id
where keywords_master.keyword = 'sports'
and keywords_master.keyword in (select keywords from Main
left join title_keyword_link on Main.id = title_keyword_link.title_id
left join keywords_master on title_keyword_link.keyword_id = keywords_master.id
where keywords_master.keyword = 'opinion')


^This code causes MySQL Workbench to crash.

Answer

I think you could use an inner join between the two result table

 select * from (

    select * from Main
    left join title_keyword_link on Main.id = title_keyword_link.title_id
    left join keywords_master on title_keyword_link.keyword_id = eywords_master.id
    where keywords_master.keyword = 'opinion'

  ) t1 
 inner join (
    select * from Main
    left join title_keyword_link on Main.id = title_keyword_link.title_id
    left join keywords_master on title_keyword_link.keyword_id = eywords_master.id
    where keywords_master.keyword = 'sports'
  ) t2 on.t1.id = t2.id
Comments