?
Solved

how to use group by in mulity table query?

Posted on 2004-08-31
7
Medium Priority
?
1,614 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

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 …
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
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 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.
Suggested Courses

765 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