CODEWITHSUNDEEP
mysqljoinleft-joininner-join
Surjit Sidhu
Surjit Sidhu

Reputation: 387

how to use JOINs instead of IN clause

I have below query that works fine, but it used IN clause. is there way to replace IN clause with JOINs ?

SELECT p.*
FROM products as p
LEFT JOIN `product_categories` AS `pc` ON (`pc`.`product_id` = `p`.`product_id`)
WHERE pc.category_id IN (SELECT node.category_id 
FROM categories AS node, categories AS parent
WHERE node.left_node 
BETWEEN parent.left_node AND parent.right_node
AND parent.category_id = 6 ORDER BY node.left_node)

Here the tables structure I have to work with, it works fine with IN clause, but I don't know, to get same results using JOINs instead of IN clause

--
-- Table structure for table `categories`
--

CREATE TABLE IF NOT EXISTS `categories` (
  `category_id` int(11) NOT NULL,
  `name` varchar(20) NOT NULL,
  `left_node` int(11) NOT NULL,
  `right_node` int(11) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `categories`
--

INSERT INTO `categories` (`category_id`, `name`, `left_node`, `right_node`) VALUES
(1, 'electronics', 1, 20),
(2, 'televisions', 2, 9),
(3, 'tube', 3, 4),
(4, 'lcd', 5, 6),
(5, 'plasma', 7, 8),
(6, 'portable electronics', 10, 19),
(7, 'mp3 players', 11, 14),
(8, 'flash', 12, 13),
(9, 'cd players', 15, 16),
(10, '2 way radios', 17, 18);

-- --------------------------------------------------------

--
-- Table structure for table `products`
--

CREATE TABLE IF NOT EXISTS `products` (
  `product_id` int(11) NOT NULL,
  `name` varchar(40) DEFAULT NULL,
  `cat_id` int(11) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `products`
--

INSERT INTO `products` (`product_id`, `name`, `cat_id`) VALUES
(1, '20" TV', 3),
(2, '36" TV', 3),
(3, 'Super-LCD 42"', 4),
(4, 'Ultra-Plasma 62"', 5),
(5, 'Value Plasma 38"', 5),
(6, 'Power-MP3 5gb', 7),
(7, 'Ipod 4gb', 8),
(8, 'Porta CD', 9),
(9, 'Walkman', 9),
(10, 'Family Talk 360', 10);

-- --------------------------------------------------------

--
-- Table structure for table `product_categories`
--

CREATE TABLE IF NOT EXISTS `product_categories` (
  `product_id` int(11) NOT NULL,
  `category_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `product_categories`
--

INSERT INTO `product_categories` (`product_id`, `category_id`) VALUES
(1, 3),
(2, 3),
(3, 4),
(4, 5),
(5, 5),
(6, 7),
(7, 8),
(8, 9),
(9, 9),
(10, 10);

Upvotes: 0

Views: 664

Answers (1)

Rahul
Rahul

Reputation: 77876

You need to JOIN on node.category_id to convert it like

SELECT p.*
FROM products as p
LEFT JOIN `product_categories` AS `pc` ON `pc`.`product_id` = `p`.`product_id`
JOIN categories AS node ON pc.category_id = node.category_id
JOIN categories AS parent ON node.left_node BETWEEN parent.left_node AND parent.right_node
WHERE parent.category_id = 6
ORDER BY node.left_node;

Upvotes: 1

Related Questions