1

I have 3 tables.

tb_employees:

+---------------+---------------+
|  id_employee  |   employee    |
+---------------+---------------+
|     1         | Robert Tomson |
|     2         | Jhonatan Weg  |
|     3         |   Eva Uhte    |
+---------------+---------------+

tb_requirements:

+---------------+-----------------+
|  id_requirem  |    requirem     |
+---------------+-----------------+
|     11        |      Photo      |
|     12        | Criminal Record |
|     13        |  Shooting Test  |
+---------------+-----------------+

tb_details:

+---------------+-----------------+---------------+
|  id_details   |  id_employee    |  id_requirem  |
+---------------+-----------------+---------------+
|     21        |       1         |       11      |
|     22        |       1         |       12      |
|     23        |       1         |       13      |
|     24        |       2         |       12      |
|     25        |       2         |       13      |
|     26        |       3         |       11      |
|     27        |       3         |       13      |
+---------------+-----------------+---------------+

What I have to do is a SELECT of requirem (the description) and an specific employee (for exm. WHERE id_employee = '2') and ADD a 'virtual column' that shows if the employee has that requirement or not.

Like This: WHERE id_employee = '2'

+----------------+-----------------+
|    requirem    | virtual_column  |
+----------------+-----------------+
|     Photo      |       0         |
|Criminal Record |       1         |
| Shooting Test  |       1         |
+---------------+------------------+

WHERE id_employee = '3'

+----------------+-----------------+
|    requirem    | virtual_column  |
+----------------+-----------------+
|     Photo      |       1         |
|Criminal Record |       0         |
| Shooting Test  |       1         |
+---------------+------------------+

Where 0 = Employee doesn't have that requirement, 1 = Employee has that requirement : from the tb_detail.

I really don't have an idea how to do this.

Can u help me please?

Thank you for answers.

Improve this question
2
  • What kind of job requires a criminal record and a shooting test? Mafia hitman? Commented Nov 6, 2013 at 17:50
  • Hahaha, it was just a test to understand the 'sql logic' :) Commented Nov 6, 2013 at 18:01

1 Answer 1

Reset to default
3

The key to achieving your goal is an OUTER JOIN

SELECT r.requirem, (d.id_requirem IS NOT NULL) virtual_column
  FROM tb_requirements r LEFT JOIN tb_details d
    ON r.id_requirem = d.id_requirem
   AND d.id_employee = 2

Output for id_employee = 2:

|        REQUIREM | VIRTUAL_COLUMN |
|-----------------|----------------|
|           Photo |              0 |
| Criminal Record |              1 |
|   Shooting Test |              1 |

Output for id_employee = 3:

|        REQUIREM | VIRTUAL_COLUMN |
|-----------------|----------------|
|           Photo |              1 |
| Criminal Record |              0 |
|   Shooting Test |              1 |

Here is SQLFiddle demo

Further reading:

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.