Home » SQL & PL/SQL » SQL & PL/SQL » Query Help
Query Help [message #215166] Fri, 19 January 2007 09:24 Go to next message
lowcash75
Messages: 67
Registered: February 2006
Member
I'm trying to run the following query to return back a user who satisfies both of these conditions of expertise id of 1 and 2. So i want to return back a user_id who has both of those expertise. Running this query obviously is not returning anything back. Please help!

select a.user_id
from usera, user_expertise b
where a.user_id = b.user_id
and b.expertise_id = 1 and b.expertise_id = 2

Re: Query Help [message #215171 is a reply to message #215166] Fri, 19 January 2007 09:30 Go to previous messageGo to next message
A Ikramur Rahman
Messages: 81
Registered: May 2004
Member
select user_id
from user
where user_id in
(select user_id
from user_expertise
where expertise_id = 1)
and user_id in
(select user_id
from user_expertise
where expertise_id = 2)
Re: Query Help [message #215172 is a reply to message #215166] Fri, 19 January 2007 09:31 Go to previous messageGo to next message
prashant_pathak
Messages: 263
Registered: February 2006
Location: California,US
Senior Member
select a.user_id
from usera, user_expertise b
where a.user_id = b.user_id
and b.expertise_id = 1 
union
select a.user_id
from usera, user_expertise b
where a.user_id = b.user_id
and  b.expertise_id = 2
Re: Query Help [message #215261 is a reply to message #215166] Sat, 20 January 2007 04:20 Go to previous messageGo to next message
Utham
Messages: 25
Registered: June 2003
Junior Member

How about this...

select a.user_id
from user a, user_expertise b
where a.user_id = b.user_id
and b.expertise_id in (1,2)
Re: Query Help [message #215265 is a reply to message #215261] Sat, 20 January 2007 05:02 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
that would select any user that has either 1 or 2. Not only those that have BOTH 1 and 2.

[Updated on: Sat, 20 January 2007 05:02]

Report message to a moderator

Re: Query Help [message #215267 is a reply to message #215166] Sat, 20 January 2007 05:19 Go to previous message
Utham
Messages: 25
Registered: June 2003
Junior Member

Yes....not with BOTH 1 and 2 sorry...

SQL> select * from userac
2 ;

USERID
----------
100
200
300

SQL> select * from user_expertise;

USERID EX
---------- --
100 1
200 2
200 1
300 2

SQL> select a.userid
2 from userac a, user_expertise b
3 where a.userid = b.userid
4 and b.expertise = '1'
5 INTERSECT
6 select a.userid
7 from userac a, user_expertise b
8 where a.userid = b.userid
9 and b.expertise = '2';

USERID
----------
200
Previous Topic: SQL Query
Next Topic: The follwing function is giving an error (numeric or value error) in returning a value
Goto Forum:
  


Current Time: Sat Dec 03 00:49:46 CST 2016

Total time taken to generate the page: 0.14699 seconds