Home » SQL & PL/SQL » SQL & PL/SQL » sql query
sql query [message #305777] Wed, 12 March 2008 00:22 Go to next message
tannad
Messages: 43
Registered: January 2008
Location: mumbai
Member
Hi all ..
I have one table as test

CREATE TABLE skill
(skillid number(10),candidateid number(10));

in that the values are
SKILLID CANDIDATEID
-------- -----------
1 6
2 6
1 7
3 8
1 4
2 4
1 5
2 9
1 9
2 22
1 33
2 2

now I want the data which contain the candidate who have the
the skill set id 1 and 2 both;
If the candidate have only one skill id suppose 1 then it will ignore.

data will appeare as

SKILLID CANDIDATEID
-------- -----------
1 6
2 6
1 4
2 4


Means the candidate who have both skill 1and 2 will only show.

Can you tell mi what is the query for this...

Thanks...

Re: sql query [message #305783 is a reply to message #305777] Wed, 12 March 2008 00:43 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
candidateid 9 also has skillid 1,2 but it doesn't appear in the output.Can you post what you tried and where you are facing the problem.

Use :Group by and Having.


regards,

[Updated on: Wed, 12 March 2008 00:45]

Report message to a moderator

Re: sql query [message #305786 is a reply to message #305783] Wed, 12 March 2008 00:47 Go to previous messageGo to next message
tannad
Messages: 43
Registered: January 2008
Location: mumbai
Member
Yes I candidate 9 is also appear
in the list..can you tell mi the query please

[Updated on: Wed, 12 March 2008 00:54]

Report message to a moderator

Re: sql query [message #305788 is a reply to message #305777] Wed, 12 March 2008 01:00 Go to previous messageGo to next message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
Quote:
Can you post what you tried and where you are facing the problem.

Use :Group by and Having.



regards,
Re: sql query [message #305789 is a reply to message #305777] Wed, 12 March 2008 01:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Clues have been given, I just add you need a subquery or inline view and you may also use an analytic function.

Now please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Always post your Oracle version (4 decimals).

Regards
Michel
Re: sql query [message #305811 is a reply to message #305788] Wed, 12 March 2008 02:00 Go to previous messageGo to next message
tannad
Messages: 43
Registered: January 2008
Location: mumbai
Member
Hii
I got the answer by using this query

select candidateid from test
where skillid=1
intersect
select candidateid from test
where skillid=2;


But If my Skillset is multiple as (1,2,3,4,..to 50)
I have to write the intersect for many time for the skillset
can I write any procedure for this where I will give the skillset
id and I will get the candidates who have the all the skill set ?

Tannad
Re: sql query [message #305813 is a reply to message #305811] Wed, 12 March 2008 02:09 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
@tannad:you have been given clues so try to make use of that.Why do you want to use a PROCEDURE when you can achive the same thing in SQL?

regards,
Re: sql query [message #305821 is a reply to message #305777] Wed, 12 March 2008 02:31 Go to previous messageGo to next message
kecd_deepak
Messages: 52
Registered: December 2007
Member
Hello,
You use this also..........

But ....i need more optimize solution...............For this

SELECT candidateid,skillid
FROM skill
WHERE candidateid IN ( SELECT candidateid
FROM skill
WHERE skillid IN (1,2)
GROUP BY candidateid
HAVING COUNT(candidateid) > 1 )
Re: sql query [message #305824 is a reply to message #305821] Wed, 12 March 2008 02:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Optimization depends on many things like indexes, row numbers, number of distinct values, of nulls...
As I said, you can also do it with an inline view or analytic function.

Regards
Michel
Re: sql query [message #305871 is a reply to message #305777] Wed, 12 March 2008 05:01 Go to previous messageGo to next message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
hi


i am a newbie. Using the following i achieved the desired
result set.

select candidateid,skillid from skillset where
candidateid in (select candidateid from (
select candidateid,count(skillid) rn from
skillset where skillid in (1,2) group by
candidateid) where rn=2)

how is it?

yours
dr.s.raghunathan
Re: sql query [message #305885 is a reply to message #305777] Wed, 12 March 2008 05:26 Go to previous messageGo to next message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
Quote:
Now please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.



regards,
Re: sql query [message #305887 is a reply to message #305871] Wed, 12 March 2008 05:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There are many ways:
select * 
from t
where t.id in (select t.id
               from t
               where ...
               group by t.id
               having count(*) = 2)
/
select t1.*
from t t1,
     (select t.id
      from t
      where ...
      group by t.id
      having count(*) = 2) t2
where t1.id = t2.id 
/
select * from t where <one condition>
intersect 
select * from t where <another condition>
/
select *
from (select t.*,
             sum(case when <conditions> then 1 end) 
               over (partition by t.id) cnt
      from t)
where cnt = 2
/

And surely many others that don't come in my mind at first thought.

Regards
Michel
Re: sql query [message #305906 is a reply to message #305887] Wed, 12 March 2008 05:49 Go to previous messageGo to next message
daniel_india
Messages: 4
Registered: March 2008
Junior Member
hi,

i tried one of the above solutions which u mentioned.
but, suppose candidateid=22 having skill id=2 as a duplicate record,
then it is returning candiateid 22 also.
eg:

SELECT t1.*
FROM skill t1,
(SELECT skill.candidateid,COUNT(*)
FROM skill
WHERE skillid IN (1,2)
GROUP BY skill.candidateid
HAVING COUNT(*) = 2) t2
WHERE t1.candidateid = t2.candidateid
Re: sql query [message #305910 is a reply to message #305906] Wed, 12 March 2008 05:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, count is not the correct way if (id1,id2) is not a unique key, use sum(case ...) instead in this case.

Regards
Michel
Re: sql query [message #305918 is a reply to message #305871] Wed, 12 March 2008 06:05 Go to previous messageGo to next message
tannad
Messages: 43
Registered: January 2008
Location: mumbai
Member
This sql whill return only result for candidate 4, and 9
I want the the query such that
I have multple skillsetid from (1 to 50)
If I give the in skillsetid (1 2,3) then the result will giving the candidateid which having there both 1,2,3 skillset..
in this way I can search the candidate who have multiple skillset..

and this skill set I want to give in substitution variable (&)

means only I have to give the skillset id as 1,2 ,3..
then is will give the desire candidate who have all skill set which we are passing..
Re: sql query [message #305921 is a reply to message #305813] Wed, 12 March 2008 06:07 Go to previous messageGo to next message
tannad
Messages: 43
Registered: January 2008
Location: mumbai
Member
As I can pass the skill set ids.. in the procedure
Re: sql query [message #305922 is a reply to message #305918] Wed, 12 March 2008 06:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Work on your query.
For the next point, search from varying in-list or the like.

Regards
Michel
Re: sql query [message #305934 is a reply to message #305777] Wed, 12 March 2008 06:25 Go to previous messageGo to next message
brintha
Messages: 28
Registered: August 2007
Junior Member
hi,
try using this.

 select candidateid from 
(select candidateid,count(distinct skillid) cnt 
 from skill where 
 skillid in ('1','2') 
 group by candidateid) 
where cnt=2 
 


here you can change the cnt value to see if the candidate has all the skillids that are in 'in' clause and distinct will eliminate the duplicate skillid.

regards,
brintha
Re: sql query [message #305955 is a reply to message #305934] Wed, 12 March 2008 07:19 Go to previous messageGo to next message
daniel_india
Messages: 4
Registered: March 2008
Junior Member
HI BRINTHA,
U GAVE SIMPLE SOLUTION.
CONGRATS.
Re: sql query [message #305958 is a reply to message #305955] Wed, 12 March 2008 07:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
But this does not give the answer you asked for in your original post.

Don't post in UPPER CASE and don't u se IM/SMS speak.

Regards
Michel

[Updated on: Wed, 12 March 2008 07:26]

Report message to a moderator

Re: sql query [message #305965 is a reply to message #305958] Wed, 12 March 2008 07:53 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
Michel Cadot wrote on Wed, 12 March 2008 13:26
don't u se IM/SMS speak.



"u se": shorthand for "you say" Smile
Re: sql query [message #305968 is a reply to message #305965] Wed, 12 March 2008 07:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is just a too short word so I added a space. Smile

Regards
Michel
Re: sql query [message #305972 is a reply to message #305968] Wed, 12 March 2008 08:04 Go to previous message
daniel_india
Messages: 4
Registered: March 2008
Junior Member
ok, i will use lower case and complete word instead of shortcuts.

bye to all
Previous Topic: wrong data of query result
Next Topic: to find oracle database name
Goto Forum:
  


Current Time: Sun Dec 04 22:30:35 CST 2016

Total time taken to generate the page: 0.10467 seconds