Need help with a Select query

I need the query to do the following:
1.  Select all from table0.
2.  table0 has key Id column that can be left join to table1-table10's Id column.
3.  table1-10 has 2nd column, anotherId, that points to table11-table20.  table1.AnoterhId = table11.Id, table2.AnotherId = table12.Id............  (I think table 11-20 are tables to resolve the many-to many relationship between table0 and table11-20)
4.  In this Select query, I need every row and column from table0 ireegardless if there is matchi in table 1-20. Table11-20 has a Name column that I would like to have in the result of this Select statement if there is a match.  
Thank you.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

select table0.*, table11.Name, table12.Name, table13.Name, table14.Name, table15.Name, table16.Name, table17.Name, table18.Name, table19.Name, table20.Name from table0
left outer join table1 on =
left outer join table2 on =
left outer join table3 on =
left outer join table4 on =
left outer join table5 on =
left outer join table6 on =
left outer join table7 on =
left outer join table8 on =
left outer join table9 on =
left outer join table10 on =   -- this should include everything from table0 no matter what, and matches from everything else.

left outer join table11 on table1.anotherid =
left outer join table12 on table2.anotherid =
left outer join table13 on table3.anotherid =
left outer join table14 on table4.anotherid =
left outer join table15 on table5.anotherid =
left outer join table16 on table6.anotherid =
left outer join table17 on table7.anotherid =
left outer join table18 on table8.anotherid =
left outer join table19 on table9.anotherid =
left outer join table20 on table10.anotherid =

I think this gets you what you want.   It's hard for me to imagine the actual data in these tables so I'm not positive, but I think this is at least close.
Kevin CrossChief Technology OfficerCommented:
Hello lapucca,

Since the join between tables 1-10 and 11-20 are for removing duplicate matches, I would think you want to get to exact matches there (INNER JOIN) and then left join to that.  The right idea above, but I would change to this.  Again, based on data this may not be correct either...just another thought.

This only makes more sense to me as if you are using LEFT JOIN to table1 for example, there is nothing to guarantee you will have a table1.anotherid to match to for table11.  Therefore, using INNER JOIN to find all table1 records that have a matching table11 record in the first place and then only selecting those that match should make this work.


select table0.*, table11.Name, table12.Name, table13.Name, table14.Name, table15.Name, table16.Name, table17.Name, table18.Name, table19.Name, table20.Name 
from table0
left outer join (table1 inner join table11 on table1.anotherid = on =
left outer join (table2 inner join table12 on table2.anotherid = on =
left outer join (table3 inner join table13 on table3.anotherid = on =
left outer join (table4 inner join table14 on table4.anotherid = on =
left outer join (table5 inner join table15 on table5.anotherid = on =
left outer join (table6 inner join table16 on table6.anotherid = on =
left outer join (table7 inner join table17 on table7.anotherid = on =
left outer join (table8 inner join table18 on table8.anotherid = on =
left outer join (table9 inner join table19 on table9.anotherid = on =
left outer join (table10 inner join table20 on table10.anotherid = on =

Open in new window

lapuccaAuthor Commented:
Table 1-10 is there to sovle the many-to-many relationship between table0 and table11-20.  I don't get where is the duplicates coming from.  Am I missing something here?
I just need to get the Name column values from table11-20 by using table1-10 that carries the id for table0 and id for table11-20.  
Please adivse how to make Select to get what I need in return.  Thank you.
Kevin CrossChief Technology OfficerCommented:
Did you try the suggestions above?  If so, what error did you get?  OR what is wrong with the display.

If you are referring to my wording, I meant where you have more than one match.  Sorry for the confusion.  Please give the queries a try and post back issues.

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
Mark WillsTopic AdvisorCommented:
if they are just name/value pairs can also do in-line queries without too much penalty - so long as they are indexed on their ID's

select table0.*
, (select top 1 name from table1  t1 inner join table11 t11 on t1.anotherid = where = as table11_Name
, (select top 1 name from table2  t2 inner join table12 t12 on t2.anotherid = where = as table12_Name
, (select top 1 name from table3  t3 inner join table13 t13 on t3.anotherid = where = as table13_Name
, (select top 1 name from table4  t4 inner join table14 t14 on t4.anotherid = where = as table14_Name
, (select top 1 name from table5  t5 inner join table15 t15 on t5.anotherid = where = as table15_Name
, (select top 1 name from table6  t6 inner join table16 t16 on t6.anotherid = where = as table16_Name
, (select top 1 name from table7  t7 inner join table17 t17 on t7.anotherid = where = as table17_Name
, (select top 1 name from table8  t8 inner join table18 t18 on t8.anotherid = where = as table18_Name
, (select top 1 name from table9  t9 inner join table19 t19 on t9.anotherid = where = as table19_Name
, (select top 1 name from table10 t10 inner join table20 t20 on t10.anotherid = where = as table20_Name
from table0
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
Query Syntax

From novice to tech pro — start learning today.