0

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 | +--------------+------------------------------------------

4
  • 1
    You need to join on a column that exists in both tables and just select the location: inner join products p on ps.productID = p.productID
    – dartonw
    Sep 28, 2014 at 14:16
  • you mean a column exist on both table ,so mean JOIN work with foreigner key column ? Sep 28, 2014 at 16:32
  • Correct. productID looks like a foreign key to me.
    – dartonw
    Sep 28, 2014 at 16:48
  • yes productID is a foreign key ,by the way is it possible to create a nono unique foreigner key column ? like in my case many products going to be stored in 'Container 1' and so many products will be stored in Container 1 and I can't reference this column because it's not unique. Sep 28, 2014 at 16:59

1 Answer 1

0

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.

1
  • regarding the new query not working with me, about the redundancy productName,brand,model are foreigner key getting there data from the products table . Sep 28, 2014 at 16:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.