mySQL join question.

Question

The following line is giving an error message.

$query = 'SELECT * FROM products AS p LEFT JOIN categories AS c USING ON c.id = p.category_id WHERE c.name = "Galleri1"
 AND p.status = "active"' ;
 $Q = $this->db->query($query);

Data base structure. CATEGORIES

 CREATE TABLE IF NOT EXISTS `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=15 ;
...
...

PRODUCT

CREATE TABLE IF NOT EXISTS `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=20 ;

Error message

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ON c.id = p.category_id WHERE c.name = "Galleri1" AND p.status = "active"' at line 1

SELECT * FROM products AS p LEFT JOIN categories AS c USING ON c.id = p.category_id WHERE c.name = "Galleri1" AND p.status = "active"

Q1. Could anyone point out my mistake plese?

Q2. Could anyone tell me how to write this in Codeigniter's Active Record class ?

Thanks in advance.

Answer 1

Use single quotes, get rid of USING and make the JOIN inner:

SELECT  *
FROM    products AS p
JOIN    categories AS c
ON      c.id = p.category_id
WHERE   c.name = 'Galleri1'
        AND p.status = 'active'

• Double quotes are used to mark reserved words which you use as table and column names. The string literals should be enclosed into single quotes.

• JOIN USING (col1) means that you have a field named col1 in both tables and want to join on it. If you don't, you should use JOIN ON

• Placing this condition c.name = 'Galleri1' into the WHERE clause makes the LEFT JOIN to return exactly same records as an INNER JOIN would. The latter is more efficient (since the optimizer can select which table to make leading in the join).

Answer 2

USING and ON are two different ways to specific which columns to perform a join with. Your query is specifying both, but it looks like you are trying to use the ON syntax. Try the following:

SELECT * FROM products AS p LEFT JOIN categories AS c ON c.id = p.category_id WHERE c.name = "Galleri1"