swa66 - 1:10 am on Feb 14, 2013 (gmt 0)
I suck at joins in the worst way
Essentially joins are not hard at all. And they are at the core of using a relational database.
You start with a table (your manufacturers), and you tell in your case your database to add next to it, the columns of another table (your series) in such a fashion that the id's match. And then you do that once more for your products.
Now you need to choose what happens if an entry in e.g. your manufacturers has no match in the series: include NULLs in the rows for the the columns that are added or not ?
-> this is the type of join that you need.
I'll presume that all producs you're interested in have series that exist and all series have manufactures as well. If not, you might want to change the type of join.
untested, but something like this should work:
SELECT m.man_name, s.series_name, p.prod_name
FROM products AS p
INNER JOIN series AS s ON s.series_id = p.prod_series
INNER JOIN manufacturers AS m ON m.man_id = s.series_man
ORDER BY m.order, s.order, p.order ;
What I'd do:
read up on joins over at wikipedia (it's really against my nature to recommend anything over there, but in this case ... )
They have a link there near the bottom to "A Visual Explanation of SQL Joins" -> it might help.
Next read up on the actual documentation of mysql (choose the right version to get the subtilities right)
And then : play a bit with it.
do some selects interactively on the database and see the effect of it all.
The really nifty stuff is that you can add columns to a list of manufactures with e.g. the number of products they have in your database, but don;t go there on your first tries, take it a step at a time.