File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes SQL Query Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "SQL Query" Watch "SQL Query" New topic

SQL Query

Pol Appan
Ranch Hand

Joined: Aug 26, 2004
Posts: 144
I am a newbie is sql.

I have two tables called test_master and test_detail.
Both tables contains pos_id as common field.
In test_detail got sub_id and to get the people under a given pos_id, I join with pos_id of test_master.

In the where condition, when I give the pos_id, it's returning only
the first level. How can I get the second level and get all the levels?

Any help is highly appreciable.

Looking forward to hear from you.


[ June 09, 2005: Message edited by: Bear Bibeault ]

Never take anything for granted because you never know when you will lose it
Bear Bibeault
Author and ninkuma

Joined: Jan 10, 2002
Posts: 62502

I edited your post to remove all the urgent urgent urgent stuff.

Please read this.

[Asking smart questions] [Bear's FrontMan] [About Bear] [Books by Bear]
Jeanne Boyarsky
author & internet detective

Joined: May 26, 2003
Posts: 31808

For the second level, you can use a nested query. For an arbitrary number of levels, you need a stored procedure (or the equivalent code in Java.)

[OCA 8 book] [Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Nicolas Stern
Ranch Hand

Joined: Apr 26, 2002
Posts: 57
Hi Cosmos,

I'm not sure I understood the table structure you described.

You can also use hierarchical queries, if your db system supports it (Oracle does : you can use a CONNECT BY clause)(see example at Oracle site).

Cheers - Nic
I agree. Here's the link:
subject: SQL Query