Results 1 to 6 of 6
  1. #1
    Join Date
    May 2015
    Posts
    9

    Lightbulb Unanswered: what is the answer?/

    There is 2 tables
    1st table student_reg (col- id, stu_name) ,
    2nd table student_details (col-date_of_birth and interview_date,stu_id)

    student_reg data.

    1 Anna
    2 smita
    3 smita
    4 madhuri
    5 madhuri
    6 madhuri
    7 devi

    student_details data.
    date of birth interview_date student_id

    1-3-2012 1-3-2012 1

    1-1-2012 21-3-2012 2

    1-2-2012 21-3-2012 3

    31-1-2011 1-6-2012 4

    31-1-2011 11-7-2012 5

    31-1-2011 21-3-2012 6

    31-1-2015 1-3-2012 7



    my question is student-id 4,5,7 is same name and same dob birth
    so last_applied_date will be of previous id interview_date

    need output is :



    student_id student_name dob_of_birth last_applied_date

    4 Madhuri 31-1-2011 Null
    5 Madhuri 31-1-2011 1-6-2012
    6 Madhuri 31-1-2011 11-7-2012


    my query no working :


    select t.*,
    CASE WHEN count(t.candidate_name)>1 and count(t.date_of_birth)>1 THEN min(c.interview_date)
    end as last_appiled_date,

    CASE WHEN cand_status= 0 THEN concat('Rejected in', " " , t.round)
    WHEN cand_status= 1 THEN concat('Selected in', " " , t.round)
    WHEN cand_status=2 THEN concat('On hold in', " " , t.round) end as Interview_status
    from candidate_registration intr inner join candidate_personal_detail c on intr.id=c.candidate_id

    left JOIN

    (
    select c.candidate_id,candidate_name,date_of_birth,c.inte rview_date,round,
    (select can_status from candidate_status where id=max(b.id)) as cand_status
    from candidate_status b right join candidate_registration a on a.id=b.candidate_id
    inner join candidate_personal_detail c on a.id=c.candidate_id
    left join interview_round i on i.id=b.round_id
    group by a.id,candidate_name, date_of_birth




    ) t
    on c.candidate_id = t.candidate_id
    and c.interview_date = t.interview_date

    group by intr.id,candidate_name, date_of_birth;

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    fix your data first
    you should not have duplicate rows of any type of data, so correct that first....
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    May 2015
    Posts
    9

    Unhappy Result

    not getting,

    i want result like that

    if the student and date of birth is same then result will be:

    student_id name date_of_birth interview_date
    4 Madhuri 31-1-2011 Null
    5 Madhuri 31-1-2011 1-6-2012
    6 Madhuri 31-1-2011 11-7-2012
    Last edited by Anu.86; 05-03-15 at 14:04.

  4. #4
    Join Date
    May 2015
    Posts
    9

    Unhappy please answer fast..

    please answer fast..

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Please fix data model first.

    ..failing that I suppose you could use a sub select to return student ids with more than one entry in the details table. Its a kludge workaround to put a syicking plaster on a crappy physical design.

    Your current design is not normalised, ferinstance date of birth is an attribute of the student table. Your student details table should probably be renamed interviews. As you have already indicated a student can have multiple interviews, but only one date of birth
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Healdem, such a stickler. You could have just pointed him to page 73.
    Dave

Tags for this Thread

Posting Permissions

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