• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

SQL Question

 
Ranch Hand
Posts: 315
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Ranch Hand
Posts: 227
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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)
reply
    Bookmark Topic Watch Topic
  • New Topic