Join three table

Dear Expert,

How can I join three table

1. Table one (dataentry)
     dataID           brandID
     d-01                   b-01
     d-02                   b-03
     d-03                   b-02
     d-04                   b-04

2. Table two (brandname)
     brandID            brandName                cateID       numStaff
     b-01                   Tiger Beer                   c-02           100
     b-02                   Heineken Beer           c-02            200
     b-03                   SBC Bank                     c-01           300
     b-04                   ANZ Bank                    c-01            400

3. Table three (brandCategory)
     cateID              cateName
     c-01                   Bank
     c-02                   Beer
     c-03                   Mobile
     c-04                   Computer

Result after query
 CateName          brandName       numStaff
 bank                    ANZ Bank           400
 bank                    SBC Bank            300
arkam chouAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
you simply join them indeed:
select c.cateName, b.brandName, b.numStaff
  from dataentry d
  join brandname b on b.brandID = d.brandID            
  join brandCategory c on c.cateID = b.cateID    

Open in new window

for your information, I used aliases in the queries, explained here:
http://www.experts-exchange.com/articles/11135/Why-should-I-use-aliases-in-my-queries.html
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
arkam chouAuthor Commented:
Hello Guy Hengel [angelIII / a3],

How can I join with table have category and subgategory name like this?

1. Table one (dataentry)
     dataID           brandID
     d-01                   b-01
     d-02                   b-03
     d-03                   b-02
     d-04                   b-04

2. Table two (brandname)
     brandID            brandName                cateID       numStaff
     b-01                   Tiger Beer                   c-02           100              
     b-02                   Heineken Beer           c-02            200
     b-03                   SBC Bank                     c-01           300
     b-04                   ANZ Bank                    c-01            400

3. Table three (brandCategory)
     cateID              cateName             location
     c-01                   Bank                     5
     c-02                   Beer                      4
     

4. BrandLocation
     location     parentLocation     Name
     1                 Null                          City
     2                 Null                          Countryside
     3                  1                               aaaa
     4                  1                               bbbb
     5                  2                               ccccc
select root.name  as ParentLocation
     , down1.name as ChildLocation
  from brandLocation as root
left outer
  join brandLocation as down1
    on down1.parentLocation = root.location
left outer
  join categories as down2
    on down2.parentLocation= down1.id
where root.parentLocation is null

Open in new window



Result after query
 CateName          brandName       numStaff     City/Country         Commune
 bank                    ANZ Bank           400               countryside          cccc
 bank                    SBC Bank            300               countryside          cccc
 Beer                   Tiger Beer            100               city                         bbbb    
 Beer                   Heineken Beer     200              city                         bbbb
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you were close, you only need to join 2 times to the brandLocation indeed to your current query:

select ...
  , down2.Name city_country
  , down1.name commune
from ...
left outer join brandLocation as down1
    on down1.location= brandCategory.location
left outer join brandLocation as down2
    on down2.location= down1.parentlocation

Open in new window

0
arkam chouAuthor Commented:
Thanks you so much.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.