Home » SQL & PL/SQL » SQL & PL/SQL » Needed query
icon5.gif   Needed query [message #234122] Mon, 30 April 2007 01:52 Go to next message
akkumar81
Messages: 144
Registered: February 2007
Location: india
Senior Member

Hi,

There are Two tables are there.

table a

id name

e1 martin

e2 king

e3 nicolas

e4 arun


Table b


eid customer

e3 c1


e1 c5


e3 c7


e3 c6

e2 c10



I want a query which employee( refer id in table b) attend more customer calls.


thanks,

Arun
Re: Needed query [message #234124 is a reply to message #234122] Mon, 30 April 2007 01:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64108
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why there are so many blank lines in your post?
Why don't you use formatting?
Why don't you tell us your Oracle version?
Why don't you tell us what you already tried?
Why don't you tell us what is blocking you?

Regards
Michel

[Updated on: Mon, 30 April 2007 01:55]

Report message to a moderator

Re: Needed query [message #234126 is a reply to message #234124] Mon, 30 April 2007 02:07 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Arun, after all these questions you have asked here, I hoped that you understood how a question should be asked here in order to get a quick reply. I've tried to rephrase your question. Have a look at it and learn from it.
how a question should be asked
Hi,

I have two tables my_emps and my_calls:

CREATE TABLE my_emps( eid   VARCHAR2(10)
                    , ename VARCHAR2(10)
                    )
/

CREATE TABLE my_calls( eid   VARCHAR2(10)
                     , cname VARCHAR2(10)
                     )
/


INSERT INTO my_emps VALUES ('e1', 'martin');
INSERT INTO my_emps VALUES ('e2', 'king');
INSERT INTO my_emps VALUES ('e3', 'nicolas');
INSERT INTO my_emps VALUES ('e4', 'arun');


INSERT INTO my_calls VALUES ('e3', 'c1');
INSERT INTO my_calls VALUES ('e1', 'c5');
INSERT INTO my_calls VALUES ('e3', 'c7');
INSERT INTO my_calls VALUES ('e3', 'c6');
INSERT INTO my_calls VALUES ('e2', 'c10');


I am trying to write a query that shows all the employee records that have more than one customer call. I have tried the following:
SELECT ....


What am I doing wrong? I am using Oracle 9.2.


MHE
Re: Needed query [message #234131 is a reply to message #234126] Mon, 30 April 2007 02:22 Go to previous messageGo to next message
akkumar81
Messages: 144
Registered: February 2007
Location: india
Senior Member

OK, micheal i follow ..

Arun
Re: Needed query [message #234132 is a reply to message #234126] Mon, 30 April 2007 02:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64108
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maarten,

Very good post, should be added as a sticky.
(but who read sticky?)

Regards
Michel
Re: Needed query [message #234139 is a reply to message #234131] Mon, 30 April 2007 02:34 Go to previous messageGo to next message
akkumar81
Messages: 144
Registered: February 2007
Location: india
Senior Member

Hi,

Anybody help me my query?
My oracle version is 9i.

Arun..



Re: Needed query [message #234141 is a reply to message #234139] Mon, 30 April 2007 02:43 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Based on the script I posted earlier, try this:
SELECT e.eid
     , e.ename
FROM   my_emps e
   ,   my_calls c
WHERE  c.eid = e.eid
GROUP  BY e.eid, e.ename HAVING COUNT(c.cname) > 1
/


MHE
Re: Needed query [message #234143 is a reply to message #234139] Mon, 30 April 2007 02:48 Go to previous messageGo to next message
tarunj
Messages: 23
Registered: April 2007
Location: Noida
Junior Member
a query that shows all the employee records that have more than one customer call.

SELECT e . *
FROM my_calls c, my_emp e
WHERE e.id = c.id
group by c.id having count(1)>1
Re: Needed query [message #234156 is a reply to message #234141] Mon, 30 April 2007 04:11 Go to previous messageGo to next message
shanthkumaar
Messages: 156
Registered: February 2007
Location: india,chennai
Senior Member

hi,


tarunj wrote
Quote:
SELECT e . *
FROM my_calls c, my_emp e
WHERE e.id = c.id
group by c.id having count(1)>1


will this query really work???

sql>SELECT e . *
FROM my_calls c, my_emps e
WHERE e.eid = c.eid
group by c.eid having count(1)>1

sql>ORA-00979: not a GROUP BY expression

regards
shanth


Re: Needed query [message #234160 is a reply to message #234122] Mon, 30 April 2007 04:26 Go to previous messageGo to next message
akkumar81
Messages: 144
Registered: February 2007
Location: india
Senior Member


hi,

Actually i want a output:

eid ename
-----------------------
e3 nicholas


Arun


Re: Needed query [message #234162 is a reply to message #234160] Mon, 30 April 2007 04:31 Go to previous messageGo to next message
tarunj
Messages: 23
Registered: April 2007
Location: Noida
Junior Member
SELECT e . *
FROM my_calls c, my_emp e
WHERE e.id = c.id
group by c.id having count(1)>1

gives u

e3 nicholos
Re: Needed query [message #234163 is a reply to message #234122] Mon, 30 April 2007 04:41 Go to previous message
akkumar81
Messages: 144
Registered: February 2007
Location: india
Senior Member



ok friend thanks.
Previous Topic: Interesting Questions
Next Topic: Doubt regarding trigger and other database objects
Goto Forum:
  


Current Time: Mon Dec 05 05:04:57 CST 2016

Total time taken to generate the page: 0.11580 seconds