MYSQL Joins are confusing, to say the least. I was successfully able to do one a few different ways and thought it be best to write down how I did it so I can do it in the future.

What I had was two different tables. One table was xcart_products_categories which stores all “productid” and matches them with which category they go to “categoryid”. The other table which was xcart_products had the “productid” and all of its information.

So if you are wanting to list all of the products from a certain category only, which I needed to list all the charms in the charms category (#344), you would need to use a MYSQL join.

Here is how I did it:

SELECT xcart_products.*, xcart_products_categories.*
FROM xcart_products_categories 
LEFT JOIN xcart_products ON xcart_products_categories.productid = xcart_products.productid  
WHERE xcart_products_categories.categoryid='344'

There is a reason I did a LEFT JOIN. If I did a regular JOIN or an INNER JOIN then I would only get the products from “xcart_products” that matched on the categoryid, but I even wanted the ones that may have had a blank productid just in case I was retarded and forgot to mark one.

JOIN three tables

Now I needed to join three tables. The actual price of the product was in a third table called “xcart_pricing”. Here is how I joined all three tables.

SELECT xcart_products.*, xcart_products_categories.*, xcart_pricing.price
FROM (xcart_products_categories LEFT JOIN xcart_products ON xcart_products_categories.productid = xcart_products.productid)
INNER JOIN xcart_pricing ON xcart_products.productid = xcart_pricing.productid 
WHERE xcart_products_categories.categoryid='344'

I did a standard INNER JOIN for the pricing table because I only needed the ones with for the productid that matched in both tables. I didn’t need any extras.