aspose file tools*
The moose likes Oracle/OAS and the fly likes Need a query (will subquery and count work?) Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "Need a query (will subquery and count work?)" Watch "Need a query (will subquery and count work?)" New topic
Author

Need a query (will subquery and count work?)

Akhilesh Trivedi
Ranch Hand

Joined: Jun 22, 2005
Posts: 1531
I have two master tables and one association table. The association table being,



I want to list all employees that are associated with one and only department "Dep1".

If I say select * from tableA where dept="Dep1"

I would get "Emp1" but that is also associated with other two departments, but I do not want that i want rows with only one association and that being to only "dep1" ( as in emp6) what should be my query.

(There is separate "emp" and "dept" master tables for employees and department respectively. But would we need them? )




Keep Smiling Always — My life is smoother when running silent. -paul
[FAQs] [Certification Guides] [The Linux Documentation Project]
Palak Mathur
Ranch Hand

Joined: Jan 29, 2007
Posts: 313

What I understand is that if dept is dep1, then emp2 and emp6 should be returned, if dep is dep2, then emp5, should be returned, and so on. Am I right? If yes, then here is your query:-



emp_dept_map is the name of the table that I had created.


Palak Mathur | My Blog | TechJaunt | What is JavaRanch? | List of All FAQs
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3611
    
  60

Other possibilities might be:

Depending on your exact setup, some of them (including Palak's one, of course) may perform better than the others.
Palak Mathur
Ranch Hand

Joined: Jan 29, 2007
Posts: 313

Martin Vajsar wrote:Other possibilities might be:



Hi Martin,

I am trying to run the above query but no data found is returned.

Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3611
    
  60

Doh! Of course, I forgot to relate the subquery records to the main query records.

This one should work:

Thanks for pointing that out, Palak!
Palak Mathur
Ranch Hand

Joined: Jan 29, 2007
Posts: 313

Martin Vajsar wrote:Doh! Of course, I forgot to relate the subquery records to the main query records.

This one should work:

Thanks for pointing that out, Palak!


I too figured that out a while ago. But still no data found.

P.S.:- No. of posts that you have done and those done by Akhilesh are same - 1354
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3611
    
  60

Unfortunately, I'll break the post count sync

This:

produces this on my database:

Could it be that in your test the case of 'Dep' values is different?
Palak Mathur
Ranch Hand

Joined: Jan 29, 2007
Posts: 313

Hi, Here are the details about the table that I created.

Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3611
    
  60

First query queries for dep2 department, not dep1 - the results differ.

Second query is my original (ie. wrong) query, does not even compile because of exist instead of exists.

Try this (should work with your input data):

These three queries produce identical (albeit differently ordered, see always use ORDER BY) output.
Akhilesh Trivedi
Ranch Hand

Joined: Jun 22, 2005
Posts: 1531
Thanks a ton Palak and Martin.

Trying...






(2 more posts to catch Martin.)
Palak Mathur
Ranch Hand

Joined: Jan 29, 2007
Posts: 313

Martin Vajsar wrote:First query queries for dep2 department, not dep1 - the results differ.

Second query is my original (ie. wrong) query, does not even compile because of exist instead of exists.

Try this (should work with your input data):

These three queries produce identical (albeit differently ordered, see always use ORDER BY) output.


Using your data. Still the query is not working. Will need to figure out why. Will do so in my freetime.
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3611
    
  60

Palak Mathur wrote:
Martin Vajsar wrote:Try this (should work with your input data):

Using your data. ...

There it is.

This illustrates it would be a good idea to provide creation scripts when an SQL query-related question is asked. We'd both have the same data set and (more importantly) I'd also test the query before posting it, which, of course, I should have done nevertheless. I'll try to amend our SQL related FAQs accordingly.

Of course, this is not meant to blame you for my sloppy posting, Akhilesh!

But back to the original question:
Akhilesh Trivedi wrote:There is separate "emp" and "dept" master tables for employees and department respectively. But would we need them?

I assume the association table contains surrogate keys/IDs of the emp and dept table; if you want to use values from emp/dept table directly in the query, you need to join them.
Akhilesh Trivedi
Ranch Hand

Joined: Jun 22, 2005
Posts: 1531
Martin Vajsar wrote:

Akhilesh Trivedi wrote:There is separate "emp" and "dept" master tables for employees and department respectively. But would we need them?

I assume the association table contains surrogate keys/IDs of the emp and dept table; if you want to use values from emp/dept table directly in the query, you need to join them.


Not required Martin. Your first out of the three queries worked here. Thanks!
Palak Mathur
Ranch Hand

Joined: Jan 29, 2007
Posts: 313

Akhilesh Trivedi wrote:
Martin Vajsar wrote:

Akhilesh Trivedi wrote:There is separate "emp" and "dept" master tables for employees and department respectively. But would we need them?

I assume the association table contains surrogate keys/IDs of the emp and dept table; if you want to use values from emp/dept table directly in the query, you need to join them.


Not required Martin. Your first out of the three queries worked here. Thanks!


Martin meant that if you want to fetch data from emp and dept tables, then you might consider using a join.

P.S.: You still have some posts to cover up!! ;)
Akhilesh Trivedi
Ranch Hand

Joined: Jun 22, 2005
Posts: 1531
Martin/Palak,

Going forward on similar thing,



if someNumber = 1

Emp2
Emp5
Emp6
Emp7

must be listed.

if someNumber = 2

Emp3
Emp4

must be listed.

And

if someNumber = 4

Emp1 must be listed.


i.e. How can I list rows based on their number of associations.

Say select * ( blah-blah ) from tablename where (number of associations) = someNumber;


Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3611
    
  60

This is actually not that complicated, you'll certainly be able to figure this out yourself. Check out a GROUP BY select with a HAVING clause (the HAVING clause let's you specify criteria on the aggregate functions).
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Need a query (will subquery and count work?)