Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

SQL Question

 
Jackie Wang
Ranch Hand
Posts: 315
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I got 2 table
SELECTED_USER
TYPE_ID USER_NAME
1A
1B
1D
2A
2A
ALL_USER
USERNAME OTHERCOLUMN
A
B
C
D
E
F

I want to have an outer join query such that the result set will be
CHECKED USER
(not null) A
(not null) B
(not null) D
null C
null E
null F
what query should i write? thanks!
 
Jackie Wang
Ranch Hand
Posts: 315
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
p.s.
when i choose type '1', that's what i want to show:
All user is displayed, but those with '1' and selected are on top.
 
Dana Hanna
Ranch Hand
Posts: 227
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What flavor of database?
If DB2/UDB/SQLServer/Oracle 9i+:
select b.type_id, b.user_name
from all_user a left outer join selected_user b
on a.user_name = b.user_name
If Oracle 8 or lower:
select b.type_id, b.user_name
from all_user a, selected_user b
where a.user_name = b.user_name(+)
Other DBs should be similar...
 
Jackie Wang
Ranch Hand
Posts: 315
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
thanks dana
I am using oracle 8i
the result is something like:
TYPE_ID USER_NAME
1..........A
1..........B
1..........D
2..........A

while I would like to have the result like:
when the typeID = 1:
TYPE_ID USER_NAME
1..........A
1..........B
1..........D
null.......C
null.......E
null.......F
but it doesn't work after i put in the condition type id = 1.
Any suggestion?
Thank so much
 
Dana Hanna
Ranch Hand
Posts: 227
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
select b.type_id, b.user_name
from all_user a, selected_user b
where a.user_name = b.user_name(+)
and (b.type_id = 1 or b.type_id is null)
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic