Need help with a Select query

Posted on 2008-11-12
Last Modified: 2012-05-05
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.
Question by:lapucca
    LVL 15

    Expert Comment

    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.
    LVL 59

    Expert Comment

    by:Kevin Cross
    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


    Author Comment

    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.
    LVL 59

    Accepted Solution

    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.
    LVL 51

    Expert Comment

    by:Mark Wills
    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

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
    Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    733 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now