CODEWITHSUNDEEP
mysql
kontakto
kontakto

Reputation: 58

MYSQL: search in an additional table with IN(select from

How can i add a second SELECT * FROM? To search in an additional table with IN( .. )

i have (is working):

Select * from Table where
    (b.id_parent in (select product_id from product_keywords where keyword='" . $search . "')) ";

i want to add:

    or (select product_id from product_keywords_NEW where keyword='" . $search . "')

Upvotes: 1

Views: 30

Answers (1)

GMB
GMB

Reputation: 222462

I would recommend exists and correlated subqueries:

select *
from mytable t
where 
    exists (
        select 1 
        from product_keywords pk 
        where pk.keyword = ? and pk.product_id = t.id_parent)
    or exists (
        select 1 
        from product_keywords_new pkn
        where pkn.keyword = ? and pkn.product_id = t.id_parent
    )

exists performs better than in on large datasets.

Note that I also changed your query so the keyword to search for is given as a parameter to the query rather than concatenated in the query string. This is more efficient, and prevents SQL injection.

Depending on your data, it might be more efficient to use union all and a single exists condition rather then or and two subquerires:

select *
from mytable t
where 
    exists (
        select 1 
        from (
            select product_id, keyword from product_keywords 
            union all 
            select product_id, keyword from product_keywords_new
        ) pk
        where pk.keyword = ? and pk.product_id = t.id_parent
    )

Upvotes: 1

Related Questions