General discussion

Locked

join

By bara ·
i do have a query which uses 4 tables to display results of multiple rows of values. In this i have a scenario of a column referring to primary key column of same table. that is i use,

"select a.up_cd, a.UP_Name, b.UP_Name
from UserProfile a LEFT OUTER JOIN UserProfile b
on a.UP_Sub_UP_Cd = b.UP_Cd"

note1: UP_Sub_UP_Cd refers to substitude user, who will also be a ordinary user. i like to display the substitute user's name by using joins.

note2:UP_Sub_UP_Cd can be blank also, meaning that a user may not have a substitute.


similiarly, i use the follwing query
"select a.up_cd, a.UP_Name, c.Lvl_Desc, d.Rol_Desc, e.Grp_Desc
from UserProfile a, Level c, Role d, UserGroup e
where a.UP_Lvl_Cd = c.Lvl_Cd
and a.UP_Rol_Cd = d.Rol_Cd
and a.UP_Grp_Cd = e.Grp_Cd"


to display level,role & similiar info.
how can i combine these 2 queries to get the result? the follwing query

"select a.up_cd, a.UP_Name, c.Lvl_Desc, d.Rol_Desc, e.Grp_Desc, b.UP_Name
from UserProfile a,Level c, Role d, UserGroup e, UserProfile b
where a.UP_Lvl_Cd = c.Lvl_Cd
and a.UP_Rol_Cd = d.Rol_Cd
and a.UP_Grp_Cd = e.Grp_Cd
and a.UP_Sub_UP_Cd = b.UP_Cd"

gives proper result for non blank values of substitute user. Can u help me in gettingmy aim?

This conversation is currently closed to new comments.

0 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Back to Software Forum
0 total posts (Page 1 of 1)  

Related Discussions

Related Forums