MYSQL multiple joins with reference table

Question

I have researched tirelessly to try and understand mysql joins with multiple tables and how reference tables come into play but to no avail. Also this is my first post so be gentle if I missed any rules.

Here are my tables:

customers | CREATE TABLE `customers` (
  `customer_num` int(11) NOT NULL AUTO_INCREMENT,
  `customer_name` varchar(50) DEFAULT NULL,
  `city` varchar(50) DEFAULT NULL,
  `state` varchar(50) DEFAULT NULL,
  `country` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`customer_num`)

orders | CREATE TABLE `orders` (
  `order_num` int(11) NOT NULL AUTO_INCREMENT,
  `order_date` date DEFAULT NULL,
  `shipped_date` date DEFAULT NULL,
  `status` varchar(15) DEFAULT NULL,
  `comments` text,
  `customer_num` int(11) DEFAULT NULL,
  PRIMARY KEY (`order_num`)

p_o   | CREATE TABLE `p_o` (
  `product_num` int(10) unsigned NOT NULL,
  `order_num` int(10) unsigned NOT NULL,
  KEY `order_num` (`order_num`)

 products | CREATE TABLE `products` (
  `product_num` int(11) NOT NULL AUTO_INCREMENT,
  `year` int(4) unsigned NOT NULL DEFAULT '2014',
  `make` varchar(20) NOT NULL,
  `model` varchar(20) NOT NULL,
  `price` int(6) NOT NULL DEFAULT '0',
  PRIMARY KEY (`product_num`)

Customers has a one-many relationship with orders, while orders references to p_o which has both product_num and order_num, and p_o also connects to products.

My goal is to make a query (that is somewhat optimal, not all selects and wheres) that will show all of this information in one table. I created this but cannot get them to show more than just two tables information.

select * from customers t1 join orders t2 on t1.customer_num=t2.customer_num;

That join shows all of the information from orders and customers, and I intend on it being inner join. I've tried all sorts of ways to get them all to join into one but none of them work, and I do believe it's on my end. Also I'm using a module that makes dynamic mysql tables and doesn't support union, but even if you have a solution that uses union I will take it.

Any help you can offer is greatly appreciated, I've been working on this for far too many hours.

Answer 1

I think you should get the information needed using the following:

SELECT  
  `orders`.`order_num`,
  `orders`.`order_date`,
  `orders`.`shipped_date`,
  `orders`.`status`,
  `orders`.`comments`,
  `orders`.`customer_num`,
  `customers`.`customer_name`,
  `customers`.`city`,
  `customers`.`state`,
  `customers`.`country`,
  `products`.`product_num`,
  `products`.`year`,
  `products`.`make`,
  `products`.`model`,
  `products`.`price`
FROM
    `orders`
    inner join `customers`
        ON `customers`.`customer_num` = `orders`.`customer_num`
    inner join `p_o`
        ON `p_o`.`order_num` = `orders`.`order_num`
    inner join `products`
        ON `products`.`product_num` = `p_o`.`product_num`

I'd like (and I hope) to help you understand the process of multiple join basing on this problem.

The in-words description of this multiple join operation could be like this:

---

I need to extract detailed information about orders i.e.: order header info, order customer detailed info and products ordered within each of the order numbers.
According to my database structure I can see that every order header from orders table has field with customer number whom the order belongs to, so I want to connect the detailed data from customers table to every order from orders table.
Then I know that I will also need the detailed specification of each product for every specific order.
My DB structure says that I can access product detailed information from products table through "connection" table p_othen I'm connecting p_o to my already-joined set {customers, orders} basing on the order_num column.
Having this information in the result set by now ({customers, orders, p_o}) I only need to connect products table to the p_o table on the product_code key.
In the end I list the columns needed from final result set consisting from four tables {customers, orders, p_o, products}.

---

I hope it could help you some way.