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

  • 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 '14 at 14:16
  • you mean a column exist on both table ,so mean JOIN work with foreigner key column ? – Osama Al-Banna Sep 28 '14 at 16:32
  • Correct. productID looks like a foreign key to me. – dartonw Sep 28 '14 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. – Osama Al-Banna Sep 28 '14 at 16:59
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.

  • regarding the new query not working with me, about the redundancy productName,brand,model are foreigner key getting there data from the products table . – Osama Al-Banna Sep 28 '14 at 16:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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