I am confused with join.

Question

I have two tables, omc_categories and omc_products.

I want to pull out categories name where omc_proudcts.category_id is equal to omc_categories.id.

I created the following sql but I am not sure which is right one.

SELECT C.Name AS CatName
   FROM omc_categories AS C
   LEFT JOIN omc_products AS P
     ON C.id = P.category_id
   WHERE P.category_id = $category_id
     AND p.status = "active"

or

SELECT C.Name AS CatName
   FROM omc_products AS P
   LEFT JOIN omc_categories AS C
     ON C.id = P.category_id
   WHERE P.category_id = $category_id
     AND p.status = "active"

Can anyone tell me which one right (if there is any) and why please. I am confused with join.

CREATE TABLE IF NOT EXISTS `omc_categories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `shortdesc` varchar(255) NOT NULL,
  `longdesc` text NOT NULL,
  `status` enum('active','inactive') NOT NULL,
  `parentid` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=43 ;

and

CREATE TABLE IF NOT EXISTS `omc_products` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `shortdesc` varchar(255) NOT NULL,
  `longdesc` text NOT NULL,
  `thumbnail` varchar(255) NOT NULL,
  `image` varchar(255) NOT NULL,
  `class` varchar(255) DEFAULT NULL,
  `grouping` varchar(16) DEFAULT NULL,
  `status` enum('active','inactive') NOT NULL,
  `category_id` int(11) NOT NULL,
  `featured` enum('true','false') NOT NULL,
  `price` float(4,2) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=74 ;

--UPDATE--

Sample data of omc_categories

INSERT INTO `omc_categories` (`id`, `name`, `shortdesc`, `longdesc`, `status`, `parentid`) VALUES
(21, 'Front top', 'front top', '', 'active', 4),
(20, 'Galleri 2', 'Galleri 2', '', 'active', 4),
...
(41, 'Trær', '', '', 'active', 27),
(42, 'newfolder', '', '', 'active', 27);

Sample data of omc_products

    INSERT INTO `omc_products` (`id`, `name`, `shortdesc`, `longdesc`, `thumbnail`, `image`, `class`, `grouping`, `status`, `category_id`, `featured`, `price`) VALUES
(1, 'Doggie', 'Cappelen forlag: New Flight', 'Doggie from New flight.', 'images/newflight_doggie_small.jpg', 'images/newflight_doggie_big.jpg', 'new-flight', 'imagebox-new', 'active', 5, 'false', 0.00),
(2, 'Jinnie', 'New flight Jinnie', '', 'images/newflight_jinnie_small.jpg', 'images/newflight_jinnie_big1.jpg', 'new-flight', 'imagebox-new', 'active', 5, 'false', 0.00),
...
...
(73, 'new image', '', '', 'images/daffodil_big.jpg.jpg', 'images/daffodil_big.jpg', '', '', 'active', 42, 'false', 0.00);

For example last line id 73 has 42 of category_id.

I want to pull out name of category "newfolder" from omc_categories.

Answer 1

Most probably, you want this:

SELECT  name
FROM    omc_categories
WHERE   id IN
        (
        SELECT  category_id
        FROM    products
        WHERE   status = 'active'
        )
        AND id = $id

This will return category's name if there are active products in this category, nothing otherwise.

Answer 2

First of all, they are both wrong for $category_id is undefined.

Secondly, if you remove the line

WHERE P.category_id = $category_id

(which is excessive) they will both be right and would be different in result unless you had category_id NOT NULL. Since it cannot be null, they're just the same. As well as inner join or outer join.

BTW it would be nice if you told instructed the database that category_id is in fact a foreign key ;)

Answer 3

If your looking to pull the categories name where omc_proudcts.category_id is equal to omc_categories.id the following should do that for you (unless I'm missing something...):

SELECT C.Name AS CatName
   FROM omc_categories AS C
   INNER JOIN omc_products AS P
     ON C.id = P.category_id
   WHERE p.status = "active"

INNER JOIN is saying JOIN these tables where these two values equal each other (identical to "WHERE C.id = P.category_id")

Answer 4

The issue with your statements, as Arthur Thomas points out, is that a left join will include all rows from the left table, using NULLs as values for the columns coming from the right table when the ON clause fails. A plain (inner) join will only include rows for which the ON clause succeeds.

SELECT DISTINCT C.Name AS CatName
   FROM omc_categories AS C
   JOIN omc_products AS P
     ON C.id = P.category_id
        AND P.category_id = $category_id
   WHERE p.status = "active"

Note you can include constant expressions in the ON clause, which reduces the size of the join (no point in adding rows you later remove).

Be careful about how you're building the statement. You don't want it vulnerable to SQL injection.

Answer 5

You want to use an inner-join..the simplest way to reconstruct your query is this:

SELECT C.Name AS CatName
   FROM omc_categories AS C
   INNER JOIN omc_products AS P
     ON C.id = P.category_id
   WHERE P.category_id = $category_id
     AND p.status = "active"

The inner join is going to give the intersection between the two tables, on the ID that is common to the two tables (id in the categoris, category_id in products).

A better way to solve your problem (show all the categories that have an active product) is this:

SELECT C.Name AS CatName
   FROM omc_categories AS C
   where exists (Select * from omc_products AS P where p.categoryID = c.id)

This is similar to the in statement.

If you wanted to know how many products you have per category, try this:

SELECT C.Name AS CatName, count(p.*), sum(case when p.status = "active" then 1 else 0 end) as numActiveProducts
   FROM omc_categories AS C
   left outer JOIN omc_products AS P
     ON C.id = P.category_id 
group by c.name