Vlad Vornicu Vlad Vornicu - 1 year ago 165
MySQL Question

Joomla 2.5 joining 3 tables

I am trying to join 3 tables and store the result.
The problem is I simply cannot seem to deal with it. Hours of Googling and searching led to nothing.

Here is the query:

$db = JFactory::getDBO();
$item_kit = array();

$q = "SELECT p.virtuemart_product_id,
pr.product_price,
pr.virtuemard_product_id,
pb.group_id
FROM #_virtuemart_products p
INNER JOIN #_virtuemart_product_prices pr ON p.virtuemart_product_id = pr.virtuemart_product_id,
INNER JOIN #_pb_group_vm_prod_xref pb ON pb.vm_product_id = pr.virtuemart_product_id";

$db->setQuery($q);
$item_kit = $db->loadObjectList();
return $item_kit;


Please notice that I am using the ID as the common element in all tables.
After all this nonsense occurs it should so something like:

if(!empty($item_kit['group_id'])){
echo 'It works';
}


Debugging resulted in discovering an SQL error near '
INNER JOIN #_virtuemart_product_prices pr ON p.virtuemart_product_id = pr.virtu...
'

Can you please help me? Or, at least, point out where am I doing stupidly wrong...
I know it's something small and easy, but I can't see it...

Answer Source

Found the problem! pr.virtuemard_product_id to pr.virtuemart_product_id and I had to manually type in the database table prefix. What silly of me. Thank you all for your time reading this question. Regards, Vlad

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download