-3

I have questions related to SELECT statement for this case: For example there are 4 tables, which are StdCandidate, CourseChoice, Course, and EntranceTest.

The tables are shown in the image --> enter image description here

Can you help me what is the query to get the expected query?

the image that I shared there is expected result of the query. I dont know how to get 1stChoice and 2ndChoice and also Test(234) and Test(123) in the same row (one record)

Mahendra Darmawiguna
  • 1
  • 1
  • You never told us what is the expected query, but in any case you most likely need to _join_ your tables together. – Tim Biegeleisen Jul 24 '16 at 23:12
  • The tables and also expected result for the query are in the image attached, Sir. @TimBiegeleisen I dont know how to get 1stChoice and 2ndChoice and also Test(234) and Test(123) in the same row (one record) I hope you can help. Thank you! – Mahendra Darmawiguna Jul 25 '16 at 02:01
  • Load the stuff in a sqlfiddle and spare us the 1hour to type it in – Drew Jul 25 '16 at 04:22

1 Answers1

0

you should check that realtionship because there is no way to relate an exam to a course, so it is not posible to tell what course belong to what exam.

this query should get you some of the data but as i said there is no real relation with the exams and courses, if there where a field named ExamCode in CourseChoose it would be quite easy to pivot the data then. thoug mysql cant do that but a procedure would just fine.

select a.id_exam,a.name, c.description,d.examcode,d.total
from stdcandidate a, coursechoose b, course c, entrancetest d
where a.id_exam = b.id_exam
  and b.id_course = c.CourseCode
  and a.id_exam = d.id_exam
  /*and d.examcode = b.ExamCode  --this doesnt exists*/;
lacripta
  • 112
  • 3
  • 15
  • In the image that I shared there is expected result of the query. I dont know how to get 1stChoice and 2ndChoice and also Test(234) and Test(123) in the same row (one record) – Mahendra Darmawiguna Jul 25 '16 at 00:47