How to correctly write a JOIN query with CodeIgniter's query builder

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);

Database 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"

Can anyone tell me how to fix my query and write it in CodeIgniter's query builder?

Quassnoi · Accepted Answer · 2025-06-09 15:22:35Z

2

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. MySQL does support using double quotes for string literals, which often leads to confusion. This behavior can (and, in my opinion, should) be disabled by enabing the setting ANSI_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 choose which table to make leading in the join).

edited Jun 9 at 15:22

answered Dec 1, 2009 at 17:45

Quassnoi

427k94 gold badges628 silver badges623 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jordan Ryan Moore · Accepted Answer · 2009-12-01 17:46:22Z

0

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"

answered Dec 1, 2009 at 17:46

Jordan Ryan Moore

6,8872 gold badges28 silver badges27 bronze badges

Comments

mickmackusa · Accepted Answer · 2025-04-29 00:11:03Z

The USING keyword has inappropriately invaded your query. Remove that keyword and the query will not break.

I agree with @Quassnoi that JOIN is better than LEFT JOIN because your WHERE clause excludes the relevance of product rows without a correlated category row.

Concerningly, many of the column names are shared between the two tables, so by greedily selecting with SELECT *, the collisions will result in data lost. For reliability and consistency, I recommend assigning prefixed aliases on all of the category columns.

return $this->db
    ->select([
        'p.*',
        'c.id cat_id',
        'c.name cat_name',
        'c.shortdesc cat_shortdesc',
        'c.longdesc cat_longdesc',
        'c.thumbnail cat_thumbnail',
        'c.image cat_image',
        'c.class cat_class',
        'c.grouping cat_grouping',
        'c.status cat_status',
        'c.category_id cat_category_id',
        'c.featured cat_featured',
        'c.price cat_price'
    ])
    ->join('categories c', 'p.category_id = c.id')
    ->get_where('products p', ['c.name' => 'Galleri1', 'p.status' => 'active'])
    ->result();

Collectives™ on Stack Overflow

How to correctly write a JOIN query with CodeIgniter's query builder

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related