?
Solved

how to use group by in mulity table query?

Posted on 2004-08-31
7
Medium Priority
?
1,623 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
  • 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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 4

Accepted Solution

by:
mottor earned 450 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 450 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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

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 …
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Suggested Courses
Course of the Month17 days, 9 hours left to enroll

831 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