Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2005
    Posts
    116

    Unanswered: Help in including 'if' statement in a query to satisfy certion condition

    Hi Guys,

    Im facing some problem constructing a query and change slightly query results to satisfy a specific condition..

    Attached is a simple er diagram
    (the er diagram doesnt make much sense, but i want to generate a query that produces the data below and its closely related to my actual er diagram)

    below is my question

    How can i construct a query that gives me ALL Names of students with its related information and must meet a condition stated below

    QUERY RESULT
    ============

    NAME | CLASSNAME | AGE | BUILDINGNUMBER
    --------------------------------------------
    Ela | php | 22 | b23
    Kit | java | 34 | -1
    Oli | oracle | 9 | b17

    There is TWO Conditions in the 'BUILDINGNUMBER' Column
    It should be calculated as below

    1) If the 'NAME' of student exists in the 'NAMEAGE' Table, then the actual BUILDINGNUMBER IS Taken from CLASS TABLE
    2) If the 'NAME' of student doesnt exists in the 'NAMEAGE' Table, then the query should return '-1' as the BUILDINGNUMBER

    I can get the results from the query below, but i dont know how to make the query to satisfy the condition stated above


    SELECT STUDENTNAME.NAME , CLASS.CLASSNAME , CLASS.BUILDINGNUMBER , STUDENTAGE.age
    FROM NAMEAGE, STUDENTNAME, CLASS, STUDENTAGE
    WHERE NAMEAGE.NAMEID = STUDENTNAME.NAMEID
    AND NAMEAGE.AGEID = STUDENTAGE.AGEID
    AND CLASS.NAMEID = STUDENTNAME.NAMEID

    Appreciate if someone could guide me here..

    thank you very much..
    Attached Thumbnails Attached Thumbnails queryHelp.jpg  

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    To me, it seems that the model is more complicated than it should be (unless there's something I don't see). Why do you have 'nameage' and 'studentage' tables at all? It suggests that a student can be of multiple ages.

    Therefore, could the whole thing be simplified, such as:

    CLASS (classid, nameid, classname, buildingnumber)
    STUDENT (nameid, name, age)

    'class.nameid' would then be a foreign key referencing 'student.nameid'.

  3. #3
    Join Date
    Feb 2005
    Posts
    116
    Quote Originally Posted by Littlefoot
    To me, it seems that the model is more complicated than it should be (unless there's something I don't see). Why do you have 'nameage' and 'studentage' tables at all? It suggests that a student can be of multiple ages.

    Therefore, could the whole thing be simplified, such as:

    CLASS (classid, nameid, classname, buildingnumber)
    STUDENT (nameid, name, age)

    'class.nameid' would then be a foreign key referencing 'student.nameid'.

    Hi Littlefoot, thanks for the reply..actually i just created this student, class diagram to explain the type of query i wish to build, we know it can be simplified

    The real diagram that i have is different and stores different info/data in the tables, but the query that i wish to build is alomost similar to the erd attached..We have been using this tables for quite sometime, and just wanted to know whether such query can be constructed to retreive the data stated in the previous post..

    So if anyone has some idea on how to build up this query, appreciate if you can guide me here..
    Last edited by a1jit; 03-14-06 at 05:53.

  4. #4
    Join Date
    Sep 2004
    Posts
    60

    Lightbulb

    You can use 'UNION' or 'EXIST' to satisfy different 'IF' conditions. But both of these are performance degrader (as far as I know).

  5. #5
    Join Date
    Jan 2005
    Location
    Green Bay
    Posts
    201
    Something like this

    Case when Name is null then -1 else CLASS.BUILDINGNUMBER end

    this will work in I think 8.2 and above I know it workes in Oracle 9

    If you have an older version you could use a decode statement.
    Not sure of syntax of this.

  6. #6
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Code:
    SELECT sn.NAME , 
           c.CLASSNAME , 
           decode(na.nameid,null,c.BUILDINGNUMBER,-1) BUILDINGNUMBER , 
           SA.age
    FROM   NAMEAGE na, 
           STUDENTNAME sn, 
           CLASS c, 
           STUDENTAGE sa
    where sn.NAMEID = na.NAMEID(+)
     AND  sn.NAMEID = c.NAMEID(+)
     AND  sn.NAMEID = sa.NAMEID;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    It sounds like you need an OUTER JOIN to NAMEAGE to "see if it's in there", then you can select:

    Code:
    SELECT studentname.name
    ,      class.classname 
    ,      CASE WHEN nameage.nameid IS NULL
                THEN -1
                ELSE class.buildingnumber 
                END AS buildingnumber
    ,      studentage.age
    FROM   studentname
    JOIN class ON class.nameid = studentname.nameid
    LEFT OUTER JOIN nameage ON nameage.nameid = studentname.nameid
    LEFT OUTER JOIN studentage ON nameage.ageid = studentage.ageid
    But I agree with Littlefoot, there is something seriously wrong with your tables! Can each CLASS really only have one STUDENTNAME?

  8. #8
    Join Date
    Feb 2005
    Posts
    116
    Quote Originally Posted by andrewst

    But I agree with Littlefoot, there is something seriously wrong with your tables! Can each CLASS really only have one STUDENTNAME?

    thanks for the reply guys, appreciate it..
    yea, the diagram seems confusing .
    actually its not a real diagram, its just used to describe the query that i plan to build..

    thankss a million..appreciate it..

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •