Link to home
Avatar of brihol44
brihol44

asked on

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

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

Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

sure that you don't have other conditions in there?
Avatar of Sean Stuber
Sean Stuber

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?
Looks ok to me.
To test this furher
add
     where  t2.category_id is null
and you should see only t1 entries without t2's.
Avatar of brihol44

ASKER

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

ASKER CERTIFIED SOLUTION
Avatar of Sean Stuber
Sean Stuber

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Oh Duh! Thanks, I'll try that :)
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
>>> 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