Well, with a many to many table (your component_to_component table...and why do you need a "tbl_" prefix when they are clearly tables?), the general rule is that you need to join to the "main" table twice.
So, for example, this would get you one record for each component_name AND matching compatible component_name:
Code:
SELECT C1.component_name,
C2.component_name AS compatibleComponentName
FROM tbl_components AS C1,
tbl_components AS C2,
tbl_component_to_component AS CTC
WHERE C1.ID = CTC.component_ID
AND C2.ID = CTC.compatible_component_id
ORDER BY C1.component_name, compatibleComponentName
So try to get that fixed in your head as the basic idea. (And notice how you can use ALIASES to make the code clearer! You must use at least one alias when a table is repeated in a query, in any case. So why not alias all the tables?)
Now... You can easily restrict that to only primary component ID #34 by either doing
Code:
SELECT C1.component_name,
C2.component_name AS compatibleComponentName
FROM tbl_components AS C1,
tbl_components AS C2,
tbl_component_to_component AS CTC
WHERE C1.ID = CTC.component_ID
AND C2.ID = CTC.compatible_component_id
AND C1.ID = 34
ORDER BY C1.component_name, compatibleComponentName
or by doing
Code:
SELECT C1.component_name,
C2.component_name AS compatibleComponentName
FROM tbl_components AS C1,
tbl_components AS C2,
tbl_component_to_component AS CTC
WHERE C1.ID = CTC.component_ID
AND C2.ID = CTC.compatible_component_id
AND CTC.component_ID = 34
ORDER BY C1.component_name, compatibleComponentName
Hopefully you can see those two are identical, of necessity, because of the
Code:
WHERE C1.ID = CTC.component_ID
condition.
So now all you have to do is join in your component_categories table:
Code:
SELECT CAT.ID, CAT.folder_path, CAT.comp_cat_name,
C1.component_name, C1.image_filepath, C1.component_category, C1.ID AS comp_id,
C2.component_name AS compatibleComponentName
FROM tbl_components AS C1,
tbl_components AS C2,
tbl_component_to_component AS CTC,
tbl_component_categories AS CAT
WHERE C1.ID = CTC.component_ID
AND C2.ID = CTC.compatible_component_id
AND C1.component_category = CAT.ID
AND CAT.ID = 1
AND C1.ID = 34
ORDER BY C.component_category, C2.compatibleComponentName
Note that of course you don't *need* to select any fields from C2. But then what was the point in getting the component_to_component table involved in the first place?
If you don't need to know ANYTHING about the compatible components, then what was wrong with simply doing
Code:
SELECT CAT.ID, CAT.folder_path, CAT.comp_cat_name,
C1.component_name, C1.image_filepath, C1.component_category, C1.ID AS comp_id
FROM tbl_components AS C1,
tbl_component_categories AS CAT
WHERE C1.component_category = CAT.ID
AND CAT.ID = 1
AND C1.ID = 34
ORDER BY C.component_category
???
Unless, of course, I have misunderstand your table relationships. It would help if you'd name fields the same when they appear in more than one table.
As a general rule, I *NEVER* name anything just "ID". (And I never put "tbl_" on table names.)
I would have done
Code:
CREATE TABLE components (
component_ID INT AUTO_INCREMENT PRIMARY KEY,
category_ID INT REFERENCES component_categories(category_ID)
component_name VARCHAR(xxx),
... etc. ...
);
CREATE TABLE component_categories (
category_ID INT AUTO_INCREMENT PRIMARY KEY,
category_name VARCHAR(xxx),
... etc. ...
);
So that then I get a NATURAL join between the components table and the component_to_component table, using the same field name. And ditto the join to component_categories.
Before you post, read our: 
