Working query, desire join

Kudose

'ello all.

Constructing queries has never been a strength of mine, but I would like to get better.

I have the following query which returns the "plugin id (pid)" of all plugins that are supposed to be on a page based on a "category id (cid)" that matches the current page.

$category = 1;
$sql = "SELECT PR.id, PR.name, PR.indexPath, CP.location";
  $sql .= " FROM ".DB_PREFIX."pluginRegistry AS PR";
  $sql .= ", ".DB_PREFIX."categoryPlugins AS CP, ".DB_PREFIX."categories AS C";
  $sql .= " WHERE CP.pid=PR.id AND C.id='$category'";

This works and returns the 2 results I expect, but I don't think this is a very efficient query. Could someone assist me? I have a feeling it will turn into a JOIN, but I don't really understand the entire construction of those queries, just that they work.

DB Schema:

TIA!

cgraz

I'm fairly new to anything more than just basic joins, but since no one responded I thought I'd give it a shot.

SELECT pr.id, pr.name, pr.indexPath, cp.location 
FROM categories AS c 
INNER JOIN categoryPlugins AS cp 
	ON cp.cid = c.id 
INNER JOIN pluginRegistry AS pr 
	ON cp.pid = pr.id 
WHERE c.id = 7;

Just replace the 7 with your id.

I typically have to actually play with a few variations to get it right (in PHPmyAdmin), so if this doesn't work and you want to send me some of your exported data I can plug in and give it another shot.

Kudose

Why should I use a join instead of my query?

I mean ... what's the point? It looks more complicated.

NogDog

A comma-separated list of table names followed by where clauses that specify what fields must match between those tables is, in fact, an inner join. Some might say that the "join" method is clearer, if that's what they're used to. (I suppose it's a bit similar to deciding whether to use a switch() or a series of if/elseif statements in your PHP code?) If you find yourself getting into other types of joins (outer, left, or right), you might find using the "inner join" method useful just to keep all your syntaxes consistent.