I'm needing help with pulling all of the categories in my table

brihol44
brihol44 used Ask the Experts™
on
Hello,

I have the code attached and I have two tables with main categories and sub-categories. I'm needing to pull all of the main categories even if there isn't a related ID_KEY in the sub-categories table.

My query now just seems to pull records that only join a sub-category id to main category id

Thank you,

Brian


SELECT *
      FROM bg_2011_categories t1
      LEFT JOIN bg_2011_sub_categories t2
             ON t1.id_category = t2.category_id

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
sure that you don't have other conditions in there?
Most Valuable Expert 2011
Top Expert 2012

Commented:
your left join syntax is correct


everything in bg_2011_categories   should be returned regardless of whether there is a row in bg_2011_sub_categories   or not.

if that's not what you're seeing,  can you post sample data that replicates the error?

Commented:
Looks ok to me.
Amazon Web Services

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Commented:
To test this furher
add
     where  t2.category_id is null
and you should see only t1 entries without t2's.

Author

Commented:
Ok.. Yeah you guys are right... I didn't see that my code paste was only part of it.

As far as tables goes, I have the main cats, sub cats and account_link table that links all of the company selections of sub-cats or sub_cat_IDs with a company ID that links to a 4th table to the company (company_listings).

All of the records pull fine except I can't seem to figure out how to pull all the main categories that don't have any linking past the sub_categories table.

Hope this makes sense...

Thanks,

Brian

SELECT *
      FROM bg_2011_categories t1
      LEFT JOIN bg_2011_sub_categories t2
             ON t1.id_category = t2.category_id
      LEFT JOIN bg_2011_account_link t3
             ON t2.id_sub_category = t3.sub_cat_id
      LEFT JOIN bg_2011_company_listings t4
             ON t3.rel_record_id = t4.record_id
      WHERE t4.active = '1'
     ORDER BY t1.category, t2.sub_category, t4.company_name

Open in new window

Most Valuable Expert 2011
Top Expert 2012
Commented:
your final WHERE effectively makes all your joins "INNER" rather than "OUTER" because the where is applied after all of the join conditions

just put that clause in the t4 join condition instead


SELECT *
      FROM bg_2011_categories t1
      LEFT JOIN bg_2011_sub_categories t2
             ON t1.id_category = t2.category_id
      LEFT JOIN bg_2011_account_link t3
             ON t2.id_sub_category = t3.sub_cat_id
      LEFT JOIN bg_2011_company_listings t4
             ON t3.rel_record_id = t4.record_id
            AND t4.active = '1'
     ORDER BY t1.category, t2.sub_category, t4.company_name

Author

Commented:
Oh Duh! Thanks, I'll try that :)

Author

Commented:
Ok, I was out of this project for a few days. My select statement above now pulls on the main categories but it doesn't pull It totally ignores the AND t4.active = '1' it's pulling all of the company listings even if they t4.active = 1 or null

Brian
Most Valuable Expert 2011
Top Expert 2012

Commented:
>>> it's pulling all of the company listings

of course!  it's an outer join.  That's the point of it.  If that condition becomes a filter
then it's effectively an inner join as described previously

however, since that's apparently not what you're expecting I'll repeat my first request.

please post sample data that replicates the results you are seeing
and post expected results.

you don't need (nor do I want) to post all of your data, just a representative number of rows from each table that I can test with

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial