lapucca
asked on
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.
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.
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 table0.id should make this work.
Regards,
mwvisa1
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 table0.id should make this work.
Regards,
mwvisa1
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 = table11.id) on table0.id = table1.id
left outer join (table2 inner join table12 on table2.anotherid = table12.id) on table0.id = table2.id
left outer join (table3 inner join table13 on table3.anotherid = table13.id) on table0.id = table3.id
left outer join (table4 inner join table14 on table4.anotherid = table14.id) on table0.id = table4.id
left outer join (table5 inner join table15 on table5.anotherid = table15.id) on table0.id = table5.id
left outer join (table6 inner join table16 on table6.anotherid = table16.id) on table0.id = table6.id
left outer join (table7 inner join table17 on table7.anotherid = table17.id) on table0.id = table7.id
left outer join (table8 inner join table18 on table8.anotherid = table18.id) on table0.id = table8.id
left outer join (table9 inner join table19 on table9.anotherid = table19.id) on table0.id = table9.id
left outer join (table10 inner join table20 on table10.anotherid = table20.id) on table0.id = table10.id
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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 = t11.id where t1.id = table0.id) as table11_Name
, (select top 1 name from table2 t2 inner join table12 t12 on t2.anotherid = t12.id where t2.id = table0.id) as table12_Name
, (select top 1 name from table3 t3 inner join table13 t13 on t3.anotherid = t13.id where t3.id = table0.id) as table13_Name
, (select top 1 name from table4 t4 inner join table14 t14 on t4.anotherid = t14.id where t4.id = table0.id) as table14_Name
, (select top 1 name from table5 t5 inner join table15 t15 on t5.anotherid = t15.id where t5.id = table0.id) as table15_Name
, (select top 1 name from table6 t6 inner join table16 t16 on t6.anotherid = t16.id where t6.id = table0.id) as table16_Name
, (select top 1 name from table7 t7 inner join table17 t17 on t7.anotherid = t17.id where t7.id = table0.id) as table17_Name
, (select top 1 name from table8 t8 inner join table18 t18 on t8.anotherid = t18.id where t8.id = table0.id) as table18_Name
, (select top 1 name from table9 t9 inner join table19 t19 on t9.anotherid = t19.id where t9.id = table0.id) as table19_Name
, (select top 1 name from table10 t10 inner join table20 t20 on t10.anotherid = t20.id where t10.id = table0.id) as table20_Name
from table0
select table0.*
, (select top 1 name from table1 t1 inner join table11 t11 on t1.anotherid = t11.id where t1.id = table0.id) as table11_Name
, (select top 1 name from table2 t2 inner join table12 t12 on t2.anotherid = t12.id where t2.id = table0.id) as table12_Name
, (select top 1 name from table3 t3 inner join table13 t13 on t3.anotherid = t13.id where t3.id = table0.id) as table13_Name
, (select top 1 name from table4 t4 inner join table14 t14 on t4.anotherid = t14.id where t4.id = table0.id) as table14_Name
, (select top 1 name from table5 t5 inner join table15 t15 on t5.anotherid = t15.id where t5.id = table0.id) as table15_Name
, (select top 1 name from table6 t6 inner join table16 t16 on t6.anotherid = t16.id where t6.id = table0.id) as table16_Name
, (select top 1 name from table7 t7 inner join table17 t17 on t7.anotherid = t17.id where t7.id = table0.id) as table17_Name
, (select top 1 name from table8 t8 inner join table18 t18 on t8.anotherid = t18.id where t8.id = table0.id) as table18_Name
, (select top 1 name from table9 t9 inner join table19 t19 on t9.anotherid = t19.id where t9.id = table0.id) as table19_Name
, (select top 1 name from table10 t10 inner join table20 t20 on t10.anotherid = t20.id where t10.id = table0.id) as table20_Name
from table0
left outer join table1 on table0.id = table1.id
left outer join table2 on table0.id = table2.id
left outer join table3 on table0.id = table3.id
left outer join table4 on table0.id = table4.id
left outer join table5 on table0.id = table5.id
left outer join table6 on table0.id = table6.id
left outer join table7 on table0.id = table7.id
left outer join table8 on table0.id = table8.id
left outer join table9 on table0.id = table9.id
left outer join table10 on table0.id = table10.id -- this should include everything from table0 no matter what, and matches from everything else.
left outer join table11 on table1.anotherid = table11.id
left outer join table12 on table2.anotherid = table12.id
left outer join table13 on table3.anotherid = table13.id
left outer join table14 on table4.anotherid = table14.id
left outer join table15 on table5.anotherid = table15.id
left outer join table16 on table6.anotherid = table16.id
left outer join table17 on table7.anotherid = table17.id
left outer join table18 on table8.anotherid = table18.id
left outer join table19 on table9.anotherid = table19.id
left outer join table20 on table10.anotherid = table20.id
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.