• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1646
  • Last Modified:

how to use group by in mulity table query?

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
azsd
Asked:
azsd
  • 3
  • 3
2 Solutions
 
pratikroyCommented:
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
 
azsdAuthor Commented:
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
 
azsdAuthor Commented:
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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
mottorCommented:
Select a.username, nvl(b.areaname,a.areacode), a.sex
From  table_user a, table_area b
Where a.areacode = b.areacode(+);
0
 
pratikroyCommented:
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
 
azsdAuthor Commented:
thanks pratikroy and mottor,
tested fine in my server~~
0
 
pratikroyCommented:
You are welcome azsd. Glad I could be of some help to you !
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now