CODEWITHSUNDEEP
phpmysqljoin
Mr Cathode
Mr Cathode

Reputation: 77

INNER JOIN results in no rows

I have product details in tne table, say products, categories and brand names in different tables say categories and brand. And I want to join these in a query. The query I use is,

$sql = "
SELECT p.pid
     , p.name
     , p.slug
     , p.category
     , c.name
     , p.brand
     , b.name
  FROM products p
  JOIN categories c
    ON c.sno = p.category
  JOIN brand
    ON b.sno = p.brand 
 WHERE p.sku=?
";

This query does not return any rows. However there are data in products, categories and brand tables.

Is there any logic failure in my query?

Products table

enter image description here

Categories table

enter image description here

Brand table

enter image description here

Upvotes: 2

Views: 679

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

This is a bit long for a comment.

For the data that you have shown, your query should work. I would start by removing the where clause and running:

SELECT products.pid, products.name, products.slug, products.category, categories.name, products.brand, brand.name
FROM `products` 
INNER JOIN `categories` on products.category = categories.sno 
INNER JOIN `brand` on products.brand = brand.sno ;

(You should run this directly on the database; if there is a lot of data, just throw in a limit 100 to see if you get anything.) If this doesn't return anything, then you have a problem with the joins. Look at the types of the fields. Are they the same? If not, fix the data structure. If they are characters, check for leading spaces and hidden characters.

If that query returns what you expect, the the problem is matching the skus. The most likely problem is the assignment of the value in php. Check that this is really doing what you want. Once again, check for leading spaces in the sku column and in the value. If this is the problem, you can use trim() to fix the problem.

Upvotes: 2

Related Questions