Home » SQL & PL/SQL » SQL & PL/SQL » Help me regarding this query
Help me regarding this query [message #185909] Fri, 04 August 2006 04:02 Go to next message
jadejajanaksinh
Messages: 3
Registered: August 2006
Location: india
Junior Member

Hi I have table as like shown below
CITY_PERSON_INFO
___________________
city_id | person_id
|-----------------|
|  1    |    1    |
|-----------------|
|  1    |    2    |
|-----------------| 
|  2    |    3    |
|-----------------|
|  1    |    4    |
|---------------- | 
|  1    |    5    |
|-----------------| 
|  3    |    6    |
|-----------------|
|  2    |    1    |
|-----------------|
|  3    |    1    |
|-----------------|


Query should display city_id which should be all for given person_id

I used following query
 select distinct city_id from city_person_info where person_id IN(1,2,4); 


Its not giving me result which I want.

For person_id 1,2,4 I want city_id which is 1 query should display only that city id which is related to all given person_id that city_id is 1.

How should I do SQL query to get only city_id 1 which is common to all given person_id that is 1,2,4.?

Thanks in advance..

Janak[/FONT][/FONT]

[Updated on: Fri, 04 August 2006 04:15]

Report message to a moderator

Re: Help me regarding this query [message #185912 is a reply to message #185909] Fri, 04 August 2006 04:13 Go to previous messageGo to next message
Littlefoot
Messages: 20897
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
One solution would be using INTERSECT:
SELECT city_id
  FROM CITY
  WHERE person_id = 1
INTERSECT
SELECT city_id
  FROM CITY
  WHERE person_id = 2
INTERSECT
SELECT city_id
  FROM CITY
  WHERE person_id = 4;
Re: Help me regarding this query [message #185914 is a reply to message #185912] Fri, 04 August 2006 04:21 Go to previous messageGo to next message
jadejajanaksinh
Messages: 3
Registered: August 2006
Location: india
Junior Member

THANKS..That is right!! But IF the number of person_id is too large then how to optimize that query or what should be other way for that query.
Re: Help me regarding this query [message #185928 is a reply to message #185914] Fri, 04 August 2006 05:53 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
select distinct city_id from (
	select a.*, count(*) over (partition by city_id) cnt
	from city_person_info a
	where person_id in (1,2,4))
where cnt = 3;


Re: Help me regarding this query [message #185937 is a reply to message #185928] Fri, 04 August 2006 06:38 Go to previous message
jadejajanaksinh
Messages: 3
Registered: August 2006
Location: india
Junior Member

ebrian
Thanks you very much.I got the result
Previous Topic: how to return multile values from function.
Next Topic: how to insert 45&56&
Goto Forum:
  


Current Time: Tue Dec 06 16:29:22 CST 2016

Total time taken to generate the page: 0.07187 seconds