Home » SQL & PL/SQL » SQL & PL/SQL » retrieving records (Oracle 8i)
retrieving records [message #449146] Sat, 27 March 2010 06:53 Go to next message
nagaraju.ch
Messages: 98
Registered: July 2007
Location: bangalore
Member
Hi,

I want to retrieve the data from ex.tables can any one give me the query how to get this.

table1:

Account_No, Account_sub_No
1234 1
1234 2
1234 3
2345 4
2345 5
2345 6
2345 7
2345 8
................
Account_no is the primary key
table2:
Account_sub_No, Description
1 Hello
2 Hi
3 No.1
4 great
5 people
.................
8 world
..........................
Account_sub_No is primary key.

Out put:

I want the data like Account_no who is having more than 3 Account_sub_no values.

But in my case need to join these two tables with other tables.
join field is Account_no from table1. there are no other fields to join.

Re: retrieving records [message #449148 is a reply to message #449146] Sat, 27 March 2010 07:10 Go to previous messageGo to next message
Yasir Hashmi
Messages: 304
Registered: April 2006
Senior Member
How can you have account_no as primary key with duplicate records??
Re: retrieving records [message #449161 is a reply to message #449146] Sat, 27 March 2010 10:11 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
1/ Please follow Posting Guidelines - OraFAQ Forum Guide.
2/ Please provided DDL (CREATE TABLE ......) for ALL tables involved.
3/ Please provided DML (INSERT INTO .......) for Test data.
4/ Please provided expected/desired results.
5/ Please Preview Message / Spell-Check Before Posting.
Re: retrieving records [message #449162 is a reply to message #449146] Sat, 27 March 2010 10:12 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
Check use of HAVING keyword in Oracle
Re: retrieving records [message #449172 is a reply to message #449146] Sat, 27 March 2010 11:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select deptno, count(*) from emp group by deptno having count(*) >= 3;
    DEPTNO   COUNT(*)
---------- ----------
        30          6
        20          5
        10          3

3 rows selected.


If you post a working Test case: create table and insert statements along with the result you want with these data, we will be able to work with them.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Regards
Michel
Re: retrieving records [message #449175 is a reply to message #449146] Sat, 27 March 2010 11:22 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
This is a basic sql question.

1) read up on GROUP BY/COUNT/HAVING

2) get a book about sql (I suggest sql for dummies)

3) we don't do class homework at OraFAQ

Kevin
Previous Topic: Procedure error
Next Topic: "Table inheritance" in Oracle
Goto Forum:
  


Current Time: Thu Dec 08 22:11:05 CST 2016

Total time taken to generate the page: 0.06522 seconds