Solved

how to use group by in mulity table query?

Posted on 2004-08-31
7
1,603 Views
Last Modified: 2012-05-05
I have two table that named table_user and table_area,
in table_user there some user records:
=========================
username,areacode,sex
=========================
azsd,0376,1
fake,0377,0
chome,0376,1
user3,0376,1
=========================

in table_area stores areacodes and their areaname
=========================
areacode,areaname
=========================
0376,xinyang
0377,nanyang
=========================

I want to make an query return back records like that:
=========================
username,areaname,sex
=========================
azsd,xinyang,1
fake,nanyang,0
chome,xinyang,1
user3,xinyang,1
=========================

which format sql phras that I would use?
I use that text and sql anlays can't pass though grammar check:
select "table_user".username, "table_area".areaname,"table_user".sex from "table_user","table_area","table_user" group by "table_user".areacode;
sure i don't think it can work.
the help files with oracle 9i have only few graphic,no more info....

thanks you read this question post by a poorman poor in english
0
Comment
Question by:azsd
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 9

Expert Comment

by:pratikroy
ID: 11939827
You have a simple requirement :

Select a.username, b.areaname, a.sex
From  table_user a, table_area b
Where a.areacode = b.areacode;

Hope this helps!
0
 

Author Comment

by:azsd
ID: 11939916
yes it works,
unluckly the where statements occured an little things in my envorenment:
i have an user record

userx,0378,1

it will discarded and only 4 rows selected....
I want have these results

azsd,xinyang,1
fake,nanyang,0
chome,xinyang,1
user3,xinyang,1
userx,0378,1

how can i redirect the areacode that no exists in table_area as areaname?
0
 

Author Comment

by:azsd
ID: 11939976
I have this idea:

Select a.username, b.areaname, a.sex
From  table_user a, table_area b
Where a.areacode = b.areacode
union
select username,areacode as areaname,sex
from table_user
where areacode not in (select areacode from table_area);

but i think "not in (select)" will be slowest in oracle commands
0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 4

Accepted Solution

by:
mottor earned 150 total points
ID: 11939992
Select a.username, nvl(b.areaname,a.areacode), a.sex
From  table_user a, table_area b
Where a.areacode = b.areacode(+);
0
 
LVL 9

Assisted Solution

by:pratikroy
pratikroy earned 150 total points
ID: 11940027
SQL> select * from table_user;

USERNAME                       AREACODE          SEX
------------------------------ ---------- ----------
azsd                           0376                1
fake                           0377                0
chome                          0376                1
user3                          0376                1
userx                          0378                1

SQL> select * from table_area;

AREACODE   AREANAME
---------- ------------------------------
0376       xinyang
0377       nanyang

SQL> select a.USERNAME, NVL(b.areaname, a.areacode), a.sex
  2  from table_user a, table_area b
  3  where a.areacode = b.areacode(+)
  4  /

USERNAME                       NVL(B.AREANAME,A.AREACODE)            SEX
------------------------------ ------------------------------ ----------
azsd                           xinyang                                 1
chome                          xinyang                                 1
user3                          xinyang                                 1
fake                           nanyang                                 0
userx                          0378                                    1

SQL>

Hope this helps !
0
 

Author Comment

by:azsd
ID: 11940070
thanks pratikroy and mottor,
tested fine in my server~~
0
 
LVL 9

Expert Comment

by:pratikroy
ID: 11940170
You are welcome azsd. Glad I could be of some help to you !
0

Featured Post

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
make null the repeated levels 2 48
Oracle cursor lifecycle inside procedure. 2 39
Need to Understand Resolution to Oracle Error ORA-00600 2 52
construct a query sql 11 42
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

732 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question