how to integrate below WHERE mysql query into joomla

[Irfan Momin]

SELECT * FROM `jos_redshop_state`
WHERE `country_id`IN ( SELECT `country_id`FROM `jos_redshop_country` WHERE `country_3_code` = 'USA')

[Florian R.]

Something like the following, using the Joomla API (not tested!):

$db = JFactory::getDbo();

$subQuery = $db->getQuery(true)

->select($db->quoteName('country_id'))

->from($db->quoteName('#__redshop_country'))

->where($db->quoteName('country_3_code') . ' = ' . $db->quote('USA'));

$query = $db->getQuery(true)

->select('*')

->from($db->quoteName('#__redshop_state'))

->where($db->quoteName('country_id')) . ' IN (' . $db->quote(subQuery) . ')');

$db->setQuery($query);

$result = $db->loadObjectList(); 

Kind regards

[Dmitry Rekun aka b2z]

Hi!

Check out this gist:
https://gist.github.com/gunjanpatel/8663333

Dmitry

[Irfan Momin]

Thanx a lot...  working perfect

[Brad Gies]

Sub queries are usually slower than joins so I would join it:

SELECT `jos_redshop_state`.* FROM `jos_redshop_state` JOIN `jos_redshop_country` ON `jos_redshop_country` .country_id = `jos_redshop_state`.country_id WHERE `jos_redshop_country`.`country_3_code` = 'USA' BUT your where clause is on the `jos_redshop_country` so it's generally better to make it the primary table in the query to give the MySQL Query compiler a better hint on how to build a the query plan, so it would be better to flip the tables and do this:
SELECT `jos_redshop_state`.* FROM `jos_redshop_country` JOIN `jos_redshop_state` ON `jos_redshop_state`.country_id = `jos_redshop_country` .country_id WHERE `jos_redshop_country`.`country_3_code` = 'USA'
That one should/might/could be faster.

Brad

On 2014-04-22 12:43 PM, Irfan Momin wrote:

SELECT * FROM `jos_redshop_state`
WHERE `country_id`IN ( SELECT `country_id`FROM `jos_redshop_country` WHERE `country_3_code` = 'USA')

-- You received this message because you are subscribed to the Google Groups "Joomla! General Development" group. To unsubscribe from this group and stop receiving emails from it, send an email to joomla-dev-gene...@googlegroups.com. To post to this group, send email to joomla-de...@googlegroups.com. Visit this group at http://groups.google.com/group/joomla-dev-general. For more options, visit https://groups.google.com/d/optout.