Home » SQL & PL/SQL » SQL & PL/SQL » Sql Query
icon5.gif  Sql Query [message #247783] Wed, 27 June 2007 03:59 Go to next message
balaji23_d
Messages: 123
Registered: February 2007
Senior Member
I have a table whose rows like this:

slno name testno
1 reni 1
2 arun 2
3 jithu 3
4 rajesh 4
5 shiju 1
6 Thomas 1
7 Thomas 2
8 Thomas 3
9 Thomas 4

many rows like above.I want to select names who have attended all the test that is testno 1,2,3,4

Looking forward to help from u
Re: Sql Query [message #247785 is a reply to message #247783] Wed, 27 June 2007 04:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Have a look at COUNT, GROUP BY, HAVING.

Regards
Michel
Re: Sql Query [message #247792 is a reply to message #247783] Wed, 27 June 2007 04:19 Go to previous messageGo to next message
balaji23_d
Messages: 123
Registered: February 2007
Senior Member
hi michel,

I tried this query, i got the desired output whtever i given the question..still I have one doubt.. I have more than 1000 rows like that and many more to come.So that query will not be feasible i think.

create table t As
2 (
3 Select 1 s, 'reni'Name , 1 test From dual Union All
4 Select 2, 'arun' , 2 From dual Union All
5 Select 3, 'jithu' , 3 From dual Union All
6 Select 4, 'rajesh', 4 From dual Union All
7 Select 5, 'shiju' , 1 From dual Union All
8 Select 6, 'Thomas', 1 From dual Union All
9 Select 7, 'Thomas', 2 From dual Union All
10 Select 8, 'Thomas', 3 From dual Union All
11 Select 9, 'Thomas', 4 From dual
12 )
13 Select a.Name,
14 a.s,
15 a.test
16 From
17 ( Select *
18 From t
19 ) a,
20 (
21 Select Name,
22 Count(Name) cnt
23 From t
24 Group By Name
25 Having Count(Name) = 4
26 ) b
27 Where a.Name = b.Name
28 /

NAME S TEST
------ ---------- ----------
Thomas 6 1
Thomas 7 2
Thomas 8 3
Thomas 9 4
SQL>

pls help us to solve this problem..
Re: Sql Query [message #247811 is a reply to message #247792] Wed, 27 June 2007 05:08 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
If you just want the names, doesn't a simple group by on itself return the desired output?

SQL> WITH t AS
  2  (
  3    SELECT 1 s, 'reni'   name, 1 test FROM DUAL UNION ALL
  4    SELECT 2 s, 'arun'   name, 2 test FROM DUAL UNION ALL
  5    SELECT 3 s, 'jithu'  name, 3 test FROM DUAL UNION ALL
  6    SELECT 4 s, 'rajesh' name, 4 test FROM DUAL UNION ALL
  7    SELECT 5 s, 'shiju'  name, 1 test FROM DUAL UNION ALL
  8    SELECT 6 s, 'Thomas' name, 1 test FROM DUAL UNION ALL
  9    SELECT 7 s, 'Thomas' name, 2 test FROM DUAL UNION ALL
 10    SELECT 8 s, 'Thomas' name, 3 test FROM DUAL UNION ALL
 11    SELECT 9 s, 'Thomas' name, 4 test FROM DUAL
 12  )
 13  SELECT NAME
 14  FROM     t
 15  GROUP BY NAME
 16  HAVING   COUNT (NAME) = 4
 17  /

NAME
------
Thomas


MHE
Re: Sql Query [message #247839 is a reply to message #247783] Wed, 27 June 2007 06:27 Go to previous messageGo to next message
muddasani.s
Messages: 10
Registered: June 2007
Junior Member

HI BALU

SELECT * FROM TABLE_NAME
WHERE (TESTNO,&NAME)IN (1,2,3,4);

......
Re: Sql Query [message #247840 is a reply to message #247839] Wed, 27 June 2007 06:30 Go to previous messageGo to next message
caliguardo
Messages: 107
Registered: February 2007
Location: Chennai
Senior Member

Quote:
SELECT * FROM TABLE_NAME
WHERE (TESTNO,&NAME)IN (1,2,3,4);


This will not fetch the desired results. OP has a different requirement.
Re: Sql Query [message #247850 is a reply to message #247792] Wed, 27 June 2007 06:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maarten answered your original question.

In addition:
Please read and follow How to format your posts and How to get a quick answer to your question: TIPS AND TRICKS
Break your lines to max 80-100 characters when you format.
Please always post your Oracle version (4 decimals).

Regards
Michel

[Updated on: Wed, 27 June 2007 07:42]

Report message to a moderator

Re: Sql Query [message #247863 is a reply to message #247850] Wed, 27 June 2007 07:03 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Michel Cadot wrote on Wed, 27 June 2007 13:42
Break your lines to max 80-100 characters when you format.

Laughing Now, that's good advice. I remember someone who didn't Embarassed.

MHE
Re: Sql Query [message #248240 is a reply to message #247863] Thu, 28 June 2007 07:19 Go to previous message
caliguardo
Messages: 107
Registered: February 2007
Location: Chennai
Senior Member

I think this is what you are expecting, balaji.
select name from table_name group by name having count(name) = (select count(distinct testno) from table_name)

[Updated on: Thu, 28 June 2007 07:20]

Report message to a moderator

Previous Topic: bind variable parsing in execute immediate
Next Topic: Create trigger and raise application for Invalid login (merged)
Goto Forum:
  


Current Time: Sun Dec 11 08:29:23 CST 2016

Total time taken to generate the page: 0.10717 seconds