Mysql – Joining a column from another table with InnoDB engine

innodbjoin;MySQL

I'm using MySQL with InnoDB and I want to join the location column from products table into a productStock table. I have this table 'products'

mysql> select * from products;
+-----------+-------------------+-------+-------+-------------------------+-------+------------------+-------------+
| productID | product_reference | brand | model | productName|price|product_description | location    |+-----------+-------------------+-------+-------+-------------------------+-------+---------------------+-------------+
|         1 | 0001              | HP    | 6800  | Cartridge Plotter Z6100 | 40.00 | HP INK CARTRIDGE    | Container 1 |
|         2 | 0002              | HP    | 7550  | HP INK CARTRIDGE YELLOW | 10.00 | HP INK CARTRIDGE    | Container 2 |
|         3 | 0003              | HP    | 8600  | HP INK CARTRIDGE CYAN   |  5.00 | HP INK CARTRIDGE    | Container 1 |
|         4 | 0004              | HP    | 8640  | HP INK CARTRIDGE RED    |  5.99 | HP INK CARTRIDGE    | Container 2 |
+-----------+-------------------+-------+-------+-------------------------+-------+---------------------+-------------+

and second table productStock

mysql> select * from productStock;
+----+-----------+-------------------+-------------------------+-------+-------+---------------+---------------+---------------+---------------------+
| id | productID | product_reference | productName             | brand | model | physicalStock | lowStockAlert | lastUpdatedBy | lastUpdateDate      |
+----+-----------+-------------------+-------------------------+-------+-------+---------------+---------------+---------------+---------------------+
|  1 |         1 | 0001              | Cartridge Plotter Z6100 | HP    | 6800  |            33 |             0 | steven        | 2014-09-25 13:36:35 |
|  2 |         2 | 0002              | HP INK CARTRIDGE YELLOW | HP    | 7550  |            20 |             0 | osama         | 2014-09-25 14:08:37 |
+----+-----------+-------------------+-------------------------+-------+-------+---------------+---------------+---------------+---------------------+

My joining code is sadly not working:

select  ps.product_reference, 
        ps.productName,
        ps.physicalStock,
        ps.location as 'location'
from productStock as ps
join products ON ps.location = products.location;

This is the error I get:

ERROR 1054 (42S22): Unknown column 'ps.location' in 'field list'

All I want is to show the location for each product by getting location from products table.
this is my show create table for products & productStock:

mysql> show create table products;
------------------------------+
| Table | Create Table |
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| products | CREATE TABLEproducts(
productIDint(10) NOT NULL AUTO_INCREMENT,
product_referencevarchar(255) NOT NULL,
brandvarchar(45) NOT NULL,
modelvarchar(45) NOT NULL,
productNamevarchar(255) NOT NULL,
pricedecimal(7,2) unsigned DEFAULT NULL,
product_descriptionvarchar(255) DEFAULT NULL,
locationvarchar(45) NOT NULL,
PRIMARY KEY (productID,product_reference,model,productName),
UNIQUE KEYproduct_reference(product_reference),
UNIQUE KEYmodel(model),
UNIQUE KEYproductName(productName)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 |
+----------+---------------------------------------------

for the productStock table:
mysql> show create table productStock;

+--------------+------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------+
| productStock | CREATE TABLEproductStock(
idint(10) NOT NULL AUTO_INCREMENT,
productIDint(10) NOT NULL,
product_referencevarchar(255) NOT NULL,
productNamevarchar(255) NOT NULL,
brandvarchar(45) NOT NULL,
modelvarchar(45) NOT NULL,
physicalStockint(10) unsigned DEFAULT '0',
lowStockAlertint(10) DEFAULT '0',
lastUpdatedByvarchar(45) NOT NULL,
lastUpdateDatetimestamp NULL DEFAULT NULL,
PRIMARY KEY (id),
UNIQUE KEYproductID(productID),
UNIQUE KEYproduct_reference(product_reference),
UNIQUE KEYproductName(productName),
UNIQUE KEYmodel(model),
CONSTRAINTproductStock_ibfk_1FOREIGN KEY (productID) REFERENCESproducts(productID) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINTproductStock_ibfk_2FOREIGN KEY (product_reference) REFERENCESproducts(product_reference) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINTproductStock_ibfk_3FOREIGN KEY (productName) REFERENCESproducts(productName) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINTproductStock_ibfk_4FOREIGN KEY (model) REFERENCESproducts(model) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 |
+--------------+------------------------------------------

Best Answer

Well unfortunately your query is wrong.

There is no location column in the ps table as you have declared, so you cannot use it as the key for joining the two tables together.

Try this and use product_reference as your joining key, as it exists in both tables:

SELECT  p.product_reference, 
        p.productName,
        ps.physicalStock,
FROM products AS p
INNER JOIN productStock AS ps ON ps.product_reference = p.product_reference;

As another hint: There is redundancy in your productStock table, e.g. productName, brand, model can be retrieved from the product table and should be dropped from the productStock table.

Related Question