CODEWITHSUNDEEP
sqloracle-databaseoracle11g
flower
flower

Reputation: 2242

How to store a sql result in Oracle when I use the result not only one time?

I have three table,t_group,t_group_linkman,t_linkman. T_group can have its parent group according to the parent_id field in t_group table,And t_linkman has its parent group according to the group_id field in t_group_linkman table.Now I want to select all the group and linkman.So I write the sql,but I find that I have search the group two times.Can any one give some suggestion to change my sql statement?

(select  ''||id as id,parent_id as p_Id,name,null mobileNum1 from t_group)  
UNION 
(select tgl.group_id|'_'||l.id as id,tgl.group_id as 
p_Id,l.name,l.mobile_Num1 from t_linkman l 
inner join t_group_linkman tgl  on tgl.LINKMAN_ID=l.id 
where tgl.GROUP_ID in(select id from t_group
) and l.STATUS='Y')

Now in my sql statement,I have search the t_group two times.

For example,the test data:

t_group table:

id |parent_id|name
---|---------|-------
1  |0        |group1
2  |0        |group2
3  |2        |group3

t_linkman table:

id|name     |mobileNum1 
--|---------|-----------
1 |linkman1 |15624157851       
2 |linkman2 |15624157852              
3 |linkman3 |15624157853

t_group_linkman table:

id|group_id | linkman_id
--|---------|-----------
1 |1        |1      
2 |2        |2              
3 |3        |3                    
4 |2        |3

And I want to return the result(all group and all linkman):

id  |parent_id|name      |mobileNum1
----|---------|----------|-----
1   |0        |group1    |null
2   |0        |group2    |null
3   |2        |group3    |null
1_1 |1        |linkman1  |15624157851       
2_2 |2        |linkman2  |15624157852       
3_3 |3        |linkman3  |15624157853       
2_4 |2        |linkman4  |15624157853

Upvotes: 0

Views: 63

Answers (1)

the.Legend
the.Legend

Reputation: 686

Likely you're looking for left join

select tg.id as tg_id, tg.parent_id as p_Id, tg.name, l.mobileNum1
from t_group tg
LEFT JOIN t_group_linkman tgl on tgl.group_id = tg.id
LEFT JOIN t_linkman l on tgl.LINKMAN_ID=L.id 
WHERE l.STATUS='Y'

Assuming that t_group and t_group_linkman have 1:1 relationship while t_group_linkman and t_linkman have 1:M

If t_group and t_group_linkman also have 1:M then switch that first join to Left Join as well

If you give some data example I could tailor the query to your needs

UPDATE 1: Looks like this is the query you've been looking for:

select to_char(tg.id), tg.parent_id, tg.name, l.mobileNum1
    from t_group tg
    inner join t_group_linkman tgl  on  tg.id=tgl.group_id
    left join t_linkman l on tgl.linkman_id=l.id
UNION ALL
select to_char(tgl.group_id) || '_' || to_char(tgl.id), tgl.group_id, l.name, l.mobileNum1
    from t_group_linkman tgl 
    left join t_linkman l on tgl.linkman_id=l.id

Upvotes: 1

Related Questions