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.
