SQL statement help problem
Hi all.
I am struggling with a sql requirement. I have a main table 1 record. Then 4 many ot many tables.
mt = main table
t1 = many to many table
t2 = many to many table
t3 = many to many table
t4 = many to many table
What i want is to get all the data from each into there columnss but now have any duplicate data from any table.
eg if t1 has 3 rows and t2 has 2 rows and t3 has 1 and t4 as 1 then when i do the select i just want the select like:
01 | S56Z | 123 | 989 | 4840713
01 | 070A | 456 | |
01 | V60A | | |
not 1 row per every posssible different possibility which is the default in sql.
Hope this makes sense to someone. I cannot think of a way to do it without using loops and table variables.
I am struggling with a sql requirement. I have a main table 1 record. Then 4 many ot many tables.
mt = main table
t1 = many to many table
t2 = many to many table
t3 = many to many table
t4 = many to many table
What i want is to get all the data from each into there columnss but now have any duplicate data from any table.
eg if t1 has 3 rows and t2 has 2 rows and t3 has 1 and t4 as 1 then when i do the select i just want the select like:
01 | S56Z | 123 | 989 | 4840713
01 | 070A | 456 | |
01 | V60A | | |
not 1 row per every posssible different possibility which is the default in sql.
Hope this makes sense to someone. I cannot think of a way to do it without using loops and table variables.
EvilPostIt
Are there any columns in these tables wich join then to eachother, or are you just trying to show records from 5 tables at the same time?
these all 5 tables have same structure?
If all the tables have the same structure you can select
t1
union
t2
union
t3
.........
It'll automaticly distinct the duplicates
t1
union
t2
union
t3
.........
It'll automaticly distinct the duplicates
Do you have to worry about intermediate tables or can you access the tables directly? If you can directly access the tables, you can do right joins explicitly on the tables you are joining, assuming the main table is the left join. For example,
select *
from main as m
right join t1 as t1
on m.key = t1.key
right join t2 as t2
on m.key t2.key
.
.
.
select *
from main as m
right join t1 as t1
on m.key = t1.key
right join t2 as t2
on m.key t2.key
.
.
.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
managed to do it. thanks all