Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2011
    Posts
    32

    Unanswered: LEFT OUTER JOIN for multiple tables

    Hello,

    I have two tables:
    Code:
    content
       content_id
       content_url
    
    category_items
       category_item_id
       parent_item_id
       content_id
       category_name
    I'm trying to create 'breadcrumb' from 'category_items' table while getting URL from 'content' table.

    It works fine when I only use 'category_items' table, but when I add 'content' table it says Unknown column 'category.parent_item_id' in 'on clause'

    Code:
    SELECT
    	node.content_url AS content_url, 
    	node1.content_url AS content_url1, 
    	node2.content_url AS content_url2, 
    	category.category_name AS category_name, 
    	category1.category_name AS category_name1, 
    	category2.category_name AS category_name2 
    FROM 
    	category_items AS category, 
    	content AS node
    
    LEFT OUTER JOIN category_items AS category1 
    	ON category1.category_item_id = category.parent_item_id 
    LEFT OUTER JOIN content AS node1 
    	ON category1.content_id = node1.content_id 
    
    LEFT OUTER JOIN category_items AS category2 
    	ON category2.category_item_id = category1.parent_item_id 
    LEFT OUTER JOIN content AS node2 
    	ON category2.content_id = node2.content_id
    
    WHERE category.category_item_id= $category_id 
    AND category.content_id = node.content_id
    ORDER by category.category_name 
    LIMIT 1
    Last edited by akrashdi; 10-09-12 at 06:28.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you are mixing the deprecated old style comma join syntax with the preferred explicit JOIN syntax

    use JOIN syntax exclusively

    thus, replace the comma with INNER JOIN
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2011
    Posts
    32
    This is awesome!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •