OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » Sql Query

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Sql Query [message #247783]

Wed, 27 June 2007 03:59

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

Report message to a moderator

Re: Sql Query [message #247785 is a reply to message #247783]

Wed, 27 June 2007 04:06

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Have a look at COUNT, GROUP BY, HAVING.

Regards
Michel

Report message to a moderator

Re: Sql Query [message #247792 is a reply to message #247783]

Wed, 27 June 2007 04:19

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..

Report message to a moderator

Re: Sql Query [message #247811 is a reply to message #247792]

Wed, 27 June 2007 05:08

Maaher
Messages: 7065
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

Report message to a moderator

Re: Sql Query [message #247839 is a reply to message #247783]

Wed, 27 June 2007 06:27

muddasani.s
Messages: 10
Registered: June 2007

Junior Member

HI BALU

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

......

Report message to a moderator

Re: Sql Query [message #247840 is a reply to message #247839]

Wed, 27 June 2007 06:30

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.

Report message to a moderator

Re: Sql Query [message #247850 is a reply to message #247792]

Wed, 27 June 2007 06:42

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

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

Maaher
Messages: 7065
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.

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

.

MHE

Report message to a moderator

Re: Sql Query [message #248240 is a reply to message #247863]

Thu, 28 June 2007 07:19

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Dec 04 19:12:45 CST 2024