How to change a MYSQL query from "IN" to "INNER JOIN"?

Question

I have this code to search for a matching result in a MYSQL database:

$where[] = 'p.id IN (
    SELECT adcfvc.advert_id
      FROM #__koparent_advert_specific_fields_values AS adcfvc
     WHERE adcfvc.advert_id = p.id
       AND adcfvc.field_name = ' . $db->Quote($sf_key) . '
       AND ' . $db->Quote(JString::strtolower($sf_value)) . ' = adcfvc.field_value
)';

I want to change the above search query from using the "IN" operator to "INNER JOIN".

Knowing that I cannot change any of the database structure in anyway, just modify the above code.

possible duplicate of [How to combine "LIKE" with "IN" in a MYSQL query?](http://stackoverflow.com/questions/32401519/how-to-combine-like-with-in-in-a-mysql-query) — Alex, Sep 04 '15 at 19:50
You'll need to post the rest of the query, because the able p is not shown. — CargoMeister, Sep 04 '15 at 19:56
This shows how to do a subquery in Joomla http://joomla.stackexchange.com/questions/1/method-for-creating-a-subquery-using-jdatabase You shouldmove this question to the other site. — Elin, Sep 05 '15 at 00:54
@Alex Although it is the same snippet, I have a different approach here. I do not want to overwhelm the community with two deep questions for a very long code. Mine here was to change the used technique from IN to INNER JOIN so that it can be easier afterwards. — MHDSKY, Sep 05 '15 at 05:21

score 0 · Answer 1 · answered Sep 04 '15 at 19:54

0

I need to see the full query, but at a guess it is going to be:

LEFT JOIN adcfvc ON adcfvc.advert_id = $table.id

Where $table is the tablename of the first table.

answered Sep 04 '15 at 19:54

MaggsWeb

3,018
1
13
23

score 0 · Answer 2 · answered Sep 04 '15 at 19:57

Simply move the subquery to an inline view alias it and join on the keys.

and it appears you could eliminate the correlated sub-query in the where clause.

SELECT ...
FROM ...
LEFT JOIN (
    SELECT adcfvc.advert_id
      FROM #__koparent_advert_specific_fields_values AS adcfvc
     WHERE adcfvc.field_name = ' . $db->Quote($sf_key) . '
       AND ' . $db->Quote(JString::strtolower($sf_value)) . ' =
           adcfvc.field_value) B 
  on B.advert_Id = p.id

score 0 · Answer 3 · answered Sep 04 '15 at 19:58

You'll need to post the rest of the query, because the able p is not shown. But what's odd about your question, is why you have an "in" at all. I think you just need to do the following:

  FROM #__koparent_advert_specific_fields_values AS adcfvc, whatever_table as p
 WHERE adcfvc.advert_id = p.id
   AND adcfvc.field_name = ' . $db->Quote($sf_key) . '
   AND ' . $db->Quote(JString::strtolower($sf_value)) . ' = adcfvc.field_value

)';

How to change a MYSQL query from "IN" to "INNER JOIN"?

3 Answers3