Results 1 to 6 of 6

Thread: SQL Query

  1. #1
    Join Date
    Sep 2006
    Posts
    9

    Angry 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

  2. #2
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    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;

  3. #3
    Join Date
    Sep 2005
    Posts
    168
    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--

  4. #4
    Join Date
    Sep 2006
    Posts
    2
    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

  5. #5
    Join Date
    Sep 2006
    Posts
    2
    it is user1.attended

  6. #6
    Join Date
    Sep 2006
    Posts
    1
    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
  •