WebEngine February 2016

Select rows according row type in another table

This is my database structure.

Member Table(Type - S: Student, T: Teacher)

+---+----+----+----+
|idx|type| id | pw |
+---+----+----+----+
| 1 | S  | A  | .. |
| 2 | S  | B  | .. |
| 3 | T  | C  | .. |
| 4 | T  | D  | .. |
| 5 | S  | E  | .. |
| 6 | S  | F  | .. |
+---+----+----+----+

Student Table

+---+-----+-----+------+
|idx|grade|class|number|
+---+-----+-----+------+
| 3 |   3 |   8 |   29 |
| 4 |   2 |  10 |   13 |
+---+-----+-----+------+

Teacher Table

+---+-------+
|idx|enabled|
+---+-------+
| 3 | N     |
| 4 | N     |
+---+-------+

I want to get member info with specific info according to member type.

my code is

$result = query("SELECT * FROM `member` WHERE `id` = '...' AND `pw` = '...'");
$member_info = fetch_obj($result);

if ($member_info->type === 'T') {
    $result = query("SELECT * FROM `teacher` WHERE `idx` = '...'");
}
else {
    $result = query("SELECT * FROM `student` WHERE `idx` = '...'");
}
$specific_info = fetch_obj($result);

But, I want to get all data in just one query request.
like:

SET @type = SELECT `type` FROM `member` WHERE `member`.`idx` = '2';

SELECT 
    CASE @type
    WHEN 'S' THEN `member`.*, `student`.*
    WHEN 'T' THEN `member`.*, `teacher`.*
    END
FROM 
    CASE @type
    WHEN 'S' THEN `member`, `student`
    WHEN 'T' THEN `member`, `teacher`
    END
WHERE
    `member`.`idx` = '2' AND
    CASE @type
    WHEN 'S' THEN
        `student`.`idx` = `member`.`idx`
    WHEN 'T' THEN
        `teacher`.`idx` = `member`.`idx`
    END
;

How can I do?

Answers


Kickstart February 2016

If the student and teacher tables have the same columns then the easiest solution is probably to UNION 2 queries together, working on the basis that one of the unioned queries will not return anything.

Something like this, although I would specify the columns I wanted returned and not use SELECT *

SELECT `member`.*, `student`.*
FROM `member`
INNER JOIN `student`
ON `student`.`idx` = `member`.`idx`
WHERE `member`.`idx` = '2' 
AND member.type = 'S'
UNION
SELECT `member`.*, `teacher`.*
FROM `member`
INNER JOIN `teacher`
ON `teacher`.`idx` = `member`.`idx`
WHERE `member`.`idx` = '2' 
AND member.type = 'T'


DRapp February 2016

Since it appears in your data the Idx column in your member table could represent both that of a student AND a teacher, I would do a double left-join. Something like (and I try to never use select * )

select
      m.idx,
      m.type,
      m.id,
      m.pw,
      case when s.idx IS NULL then 0 else 1 end as IsStudent,
      s.grade,
      s.class,
      s.number,
      case when t.idx IS NULL then 0 else 1 end as IsTeacher,
      t.enabled
   from
      Member m
         LEFT JOIN Student s
            on m.idx = s.idx
         LEFT JOIN Teacher t
            on m.idx = t.idx
   where
      m.idx = 2

Then, in your result set, you will have two "flag" (0 or 1) columns IsStudent and IsTeacher so you could conditionally use the other columns IF they are so available.

Post Status

Asked in February 2016
Viewed 3,459 times
Voted 7
Answered 2 times

Search




Leave an answer