CODEWITHSUNDEEP
mysql
Tilak Raj
Tilak Raj

Reputation: 1499

inner join not giving results as expected

Product_table

enter image description here

Product_table_link

enter image description here

enter image description here

This be the product_table data that is stored inside the database.

enter image description here

This is the product_table_link data that is present inside the DB.I was trying to join these two tables where the product code=something.For instance let us take xyz as the product.

I was hoping to get the combined results of the two without any nulls present.

I tried :

SELECT s1.* FROM (SELECT p1.* FROM product_table p1 INNER JOIN product_table_link p2 
ON p1.product_code=p2.product_code ) s1 WHERE product_code="xyz"

But the result is not the combination of the both tables rather it shows me the product_table.

CREATE TABLE `product_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `product_name` varchar(60) NOT NULL,
  `product_code` varchar(60) NOT NULL,
  `product_description` text,
  `product_type` varchar(20) NOT NULL,
  `product_image_path` varchar(60) NOT NULL,
  `product_company_name` varchar(20) NOT NULL,
  `product_company_id` varchar(60) NOT NULL,
  `product_landing_page` varchar(15) NOT NULL,
  `product_shape` varchar(20) DEFAULT NULL,
  `product_flavour` varchar(20) NOT NULL,
  `product_veg_mark` varchar(8) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique` (`product_code`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1

CREATE TABLE `product_table_link` (
  `product_code` varchar(60) NOT NULL,
  `product_weight` varchar(5) NOT NULL,
  `product_price` int(5) NOT NULL,
  `product_quantity` int(5) NOT NULL,
  PRIMARY KEY (`product_code`,`product_weight`),
  CONSTRAINT `product_table_link_ibfk_1` FOREIGN KEY (`product_code`) REFERENCES `product_table` (`product_code`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

How do i get the combined results of the two tables?

Upvotes: 0

Views: 294

Answers (2)

FallAndLearn
FallAndLearn

Reputation: 4135

Change your subquery to this

SELECT * FROM product_table p1 INNER JOIN product_table_link p2 
ON p1.product_code=p2.product_code

You were selecting just from p1.

You were doing select p1.* which will only select rows from p1. Inner join means you want to select rows from different tables on the basis of some conditions. You need to do select * which will select all the rows filtered from the join condition.

Your simplified final query. 

SELECT s1.* FROM (SELECT p1.*,p2.product_weight,p2.product_price,p2.product_quantity 
FROM product_table p1 INNER JOIN product_table_link p2 
ON p1.product_code=p2.product_code) 
    s1 WHERE s1.product_code="xyz"

Upvotes: 1

vatsal
vatsal

Reputation: 63

SELECT p1.* FROM product_table p1 INNER JOIN product_table_link p2 
ON p1.product_code=p2.product_code

In the above subquery you are selecting values from p1 only. So, you have to change your query to what FallAndLearn has written.

Upvotes: 0

Related Questions