-
SQL Query
Hello ppl...
i have a tough question for u guys...i have a table called Attended which has the following
FID number
Cname character
and a Faculty table which has
FID, Faculty name, address, phone number.
A small part of the data in the Attended table is as this :-
select * from user1.attended;
FID CNAME
----- --------------------
1 Chris
1 Database I
1 Database ala Tommy
1 Grace
1 Marty Workshop
1 Pollution
1 Safety
2 Database ala Tommy
2 Grace
2 Safety
7 RAJKUMAR
FID CNAME
----- --------------------
7 ROCKY
7 TC
8 TC
23 sports
24 Database I
88 TC
100 Chrystian
101 CS
101 Safety
101 localization
102 Physics
As u can see there are many fields in FId which are common...
Well the query is to retrieve FID and Cname for those fields whic are unique that is where count (fid) = 1 ONLY..
i have tried this one but i am only getting FID but i want both CNAME and FID...
I had tried like this
select FID from user1.attended
group by FID
having count (FID) = 1;
but when i add CNAME to the select it gives me an error...How do i add CNAME for those fields which have a UNIQUE .i.e ONLY 1 FID field and its corresponding CNAME..
ANS should have CNAME and FId only........
PLS PLS HELP ME....!!!!!!!!
Last edited by zahid_ali; 09-10-2006 at 07:02 PM.
Reason: I forgot to add another table
-
You have to use derived view
select a.FID, a.cname
from user1.attended a
join
(select FID from user1.attended
group by FID
having count (FID) = 1) b
on a.fid=b.fid;
-
SELECT a.FID, b.CNAME
FROM user1.attended a, user1.attended b
WHERE b.FID = a.FID
GROUP BY a.FID, b.CNAME
HAVING COUNT(a.FID) = 1
--HTH--
-
hi zahid
her is ur sql statement.
select cname,fid from user.attended where fid in (select fid from (select
fid,count(fid) ct from user1.attended group by fid) where ct=1);
i hope this solves ur problem. by the way the mention of the second table was useless as both the fields exist in teh same table.
tell me if it solves ur problem wahidwani@yahoo.com
bye
-
-
Hi Zahid,
I think the following query will solve your problem.
SELECT FID, CNAME FROM ATTENDED WHERE FID IN (SELECT FID FROM ATTENDED HAVING COUNT(FID)=1 GROUP BY FID);
Bye....
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|