• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Need a query (will subquery and count work?)

 
Ranch Hand
Posts: 1609
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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? )



 
Ranch Hand
Posts: 343
Mac OS X Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 343
Mac OS X Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Martin Vajsar wrote:Other possibilities might be:



Hi Martin,

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

 
Martin Vashko
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 343
Mac OS X Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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 Vashko
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 343
Mac OS X Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi, Here are the details about the table that I created.

 
Martin Vashko
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 1609
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks a ton Palak and Martin.

Trying...






(2 more posts to catch Martin.)
 
Palak Mathur
Ranch Hand
Posts: 343
Mac OS X Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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 Vashko
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 1609
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 343
Mac OS X Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 1609
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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 Vashko
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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).
 
reply
    Bookmark Topic Watch Topic
  • New Topic