asked on

Trouble with MySQL query

The attached code basically builds a list of...

Main Categories
- Sub Categories
--- Company Name

I'm needing to extend the query to not only pull the above data but to pull Main Categories with a '1' value under the reference_cat column. (AND t1.reference_cat = '1') in my code.

As of now the call will only pull main categories with sub categories and company names associated with it.

<cfquery name="get_categories" datasource="#application.dsn#" username="#application.dsnUsername#" password="#application.dsnPassword#">
	SELECT 
    	*
	FROM
    	bg_categories_2010 t1, bg_sub_categories_2010 t2, bg_advertiser_link_2010 t3, bg_advertiser_profile_2010 t4
    WHERE (t1.id_category = t2.category_id AND 
    t3.sub_cat_id = t2.id_sub_category AND 
    t4.advertiser_id = t3.advertiser_id AND 
    t4.active = '1') AND t1.reference_cat = '1'
    ORDER BY t1.category, t2.sub_category, t4.company_name
</cfquery>

Open in new window

Lee Wadwell

Hi brihol44,

You will need to change it use an OUTER/LEFT join so that main categories without sub categories etc are extracted. e.g below

lwadwell

SELECT *
  FROM bg_categories_2010 t1
  LEFT JOIN bg_sub_categories_2010 t2
         ON t1.id_category = t2.category_id
  LEFT JOIN bg_advertiser_link_2010 t3
         ON t2.id_sub_category = t3.sub_cat_id
  LEFT JOIN bg_advertiser_profile_2010 t4
         ON t3.advertiser_id = t4.advertiser_id
            AND t4.active = '1'
 WHERE t1.reference_cat = '1'
 ORDER BY t1.category, t2.sub_category, t4.company_name

Open in new window

brihol44

ASKER

Nice work! I'm able to get the list that I need with...

<cfquery name="get_categories" datasource="#application.dsn#" username="#application.dsnUsername#" password="#application.dsnPassword#">
SELECT *
FROM bg_categories_2010 t1
LEFT JOIN bg_sub_categories_2010 t2
ON t1.id_category = t2.category_id
LEFT JOIN bg_advertiser_link_2010 t3
ON t2.id_sub_category = t3.sub_cat_id
LEFT JOIN bg_advertiser_profile_2010 t4
ON t3.advertiser_id = t4.advertiser_id
AND t4.active = '1'

ORDER BY t1.category, t2.sub_category, t4.company_name
</cfquery>

however I only need to show categories to sub-categories if there is a company linked to that sub-category or if the main category has a '1' value for reference_cat.

so if I have the code that you put...

SELECT *
FROM bg_categories_2010 t1
LEFT JOIN bg_sub_categories_2010 t2
ON t1.id_category = t2.category_id
LEFT JOIN bg_advertiser_link_2010 t3
ON t2.id_sub_category = t3.sub_cat_id
LEFT JOIN bg_advertiser_profile_2010 t4
ON t3.advertiser_id = t4.advertiser_id
AND t4.active = '1'
WHERE t1.reference_cat = '1'
ORDER BY t1.category, t2.sub_category, t4.company_name

I only get the listings with reference_cat = '1' when I need to get them as well as all others.

brihol44

ASKER

I only get the listings with reference_cat = '1' when I need to get them as well as all others with company listings from categories > sub-categories.

Lee Wadwell

brihol44,

This is a twaek to the WHERE clause.

At the momemnt it has an explict ... t1.reference_cat = '1' you will need to extend that to include others ... maybe

t1.reference_cat = '1' OR t2.category_id IS NOT NULL

lwadwell

brihol44

ASKER

Getting closer! Got the list I need... I'm trying to figure out how to eliminate the main categories 2 sub-categories that don't have any company listings under them but still pulling the main categories that have the reference_cat '1' value.

Thanks

B

brihol44

ASKER

Current call...

<cfquery name="get_categories" datasource="#application.dsn#" username="#application.dsnUsername#" password="#application.dsnPassword#">
	SELECT *
      FROM bg_categories_2010 t1
      LEFT JOIN bg_sub_categories_2010 t2
             ON t1.id_category = t2.category_id
      LEFT JOIN bg_advertiser_link_2010 t3
             ON t2.id_sub_category = t3.sub_cat_id
      LEFT JOIN bg_advertiser_profile_2010 t4
             ON t3.advertiser_id = t4.advertiser_id
                AND t4.active = '1'
     WHERE t1.reference_cat = '1' OR t2.category_id IS NOT NULL
     ORDER BY t1.category, t2.sub_category, t4.company_name
</cfquery>

Open in new window

ASKER CERTIFIED SOLUTION

Lee Wadwell

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

brihol44

ASKER

You da bomb! Thanks....