trying to duct tape two queries together

[turpentyne]

I have no idea how to explain this.. I have a terribly awkward page built and I'm trying to amend a query to pull only the results from the database that match, depending on their previous choice.

 

My beginning query, pulls the first set of options:

 

SELECT tbl_component_to_component.compatible_component_ID, tbl_component_categories.ID, tbl_component_categories.folder_path, tbl_component_categories.comp_cat_name, tbl_components.component_name, tbl_components.image_filepath, tbl_components.component_category, tbl_components.ID AS comp_id
FROM tbl_components 

JOIN tbl_component_categories ON tbl_components.component_category = tbl_component_categories.ID AND tbl_component_categories.ID = $variable1

 

As the page reloads, I want to only pull selectable options that are compatible with/based on the first selection made. In the database, I have a linking table (tbl_component_to_component) that lists these connections. It has three columns: id,  the product id, and compatible product id

 

This query pulls what I need.  $variable2 is what they previously chose:

 

SELECT 
tbl_component_to_component.component_ID,
tbl_component_to_component.compatible_component_ID
FROM tbl_component_to_component
JOIN tbl_components ON tbl_component_to_component.component_ID = tbl_components.ID
JOIN tbl_component_categories ON tbl_components.component_category = tbl_component_categories.ID
WHERE tbl_component_to_component.component_ID = $variable2

 

My problem is, that I can't figure out how to combine the two queries into one - if that's even my solution.

 

I thought maybe there was a way to do this using UNION, but I can't get it to work, and I worry that will lump everything together or not work, because the two queries are pulling different number of columns?

 

Not sure any of that made sense, but at least it's a starting point.

 

 

[Barand]

If you want to do a UNION you can add nulls to the second SELECT clause to make up the column numbers