Using Inner Join to Load results from one table based on data in another table

[Tim Johns]

First let me say that I read all the documentation on Joomla.org for running a query across multiple databases. But it is very hard to understand because they use a complex situation for an example and my DB skills aren't that great. I was wondering if someone could convert the following example query I found into a jDatabase query. It makes it so it would be very easy for me to create my query in standard MySQL terms, but [like I mentioned], Joomla.org documentation just exaggerates the complexity of it to where I don't understand fully enough to write it in jDatabase terms.

[code]SELECT Employee.Name, TrainingTaken.TrainingTitle, TrainingTaken.TrainingDate
FROM Employee JOIN TrainingTaken
WHERE Employee.EmployeeID = TrainingTaken.EmployeeID[/code]

Or (same thing)...

[code]SELECT Employee.Name, TrainingTaken.TrainingTitle, TrainingTaken.TrainingDate
FROM Employee
INNER JOIN TrainingTaken ON Employee.EmployeeID = TrainingTaken.EmployeeID[/code]

My current query looks like this:

[code]$db = JFactory::getDbo();
$query = $db->getQuery(true);

$query->select($db->quoteName(array('user_id', 'cb_com_company')));
$query->from($db->quoteName('#__comprofiler'));
$query->where($db->quoteName('cb_accounttype') . ' = "Company" AND .'($db->quoteName('user_id') . ' = $userid'));

$db->setQuery($query);
$row = $db->loadAssocList();
$row_count=count($row);
$count=0;

echo "<select onchange='window.location.href=this.value'>";
echo "<option selected>Choose an Originating Company</option>";
while($row_count>$count) {
echo "<option value='/index.php?option=com_comprofiler&user=".$row[$count][user_id]."&tab=32'>"
.$row[$count][cb_com_company]."</option>";
$count++;
}
echo "</select>";[/code]

But I need to only show results from table rows in the #__comprofiler table where there is a connection between the user that's accessing the data and the data results (via JOIN the Community Builder's #__comprofiler_members table.

Hope that makes sense.

[Michael Babker]

$query->join('INNER', $db->quoteName('#__comprofiler_members') . ' ON ...');

--
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 an email to joomla-de...@googlegroups.com.
Visit this group at https://groups.google.com/group/joomla-dev-general.
For more options, visit https://groups.google.com/d/optout.

[Tim Johns]

Thanks again.  I'll give it a try.