CODEWITHSUNDEEP
mysqlsqljoin
DimitrisD
DimitrisD

Reputation: 424

retrieve data from 2 tables using conditions

I have 2 tables

+---------------------+
|       T_EQUIPMENT   |
+----+--------+---+---+
| ID | NAME   |APPROVED|
+----+--------+---+---+
| 01 | foo1   |  0    |
+----+--------+---+---+
| 02 | foo2   |  1    |
+----+--------+---+---+
| 03 | foo3   |  1    |
+----+--------+---+---+
| 04 | foo4   |  1    |
+----+--------+---+---+

+---------------------------+
|       T_HAS_EQUIPMENT     |
+----+------------+----+----+
| ID_CENTER | ID_EQUIP      |
+----+------------+----+----+
|     12    |      2        |
+----+------------+----+----+
|     3     |      3        | 
+----+------------+----+----+
|     3     |      2        |
+----+------------+----+----+
|     5     |      3        |
+----+-----------+----+----+
|     6     |      2        |
+----+------------+----+----+

I want to get the id,name of approved equipment (APPROVED=1) but also whether or not this specific center has this equipment

for example for id_center = 3 i want this 

    +---------------------+
    |       RESULT        |
    +----+--------+---+---+
    | ID | NAME   |  HAS  |
    +----+--------+---+---+
    | 02 | foo2   |  1    |
    +----+--------+---+---+
    | 03 | foo3   |  1    |
    +----+--------+---+---+
    | 04 | foo4   |  0    |
    +----+--------+---+---+

so, i retrieve all equipment, and i know whether or not a center has this equipment. Any ideas?

Upvotes: 1

Views: 127

Answers (1)

OMG Ponies
OMG Ponies

Reputation: 332781

Use:

   SELECT e.id, 
          e.name,
          CASE 
            WHEN he.id_center IS NOT NULL THEN 1 
            ELSE 0
          END AS has
     FROM T_EQUIPMENT e
LEFT JOIN T_HAS_EQUIPMENT he ON he.id_equip = e.id
                            AND he.id_center = 3
    WHERE e.approved = 1
 ORDER BY e.id

Placement of filter criteria against a table using an OUTER (LEFT, RIGHT) join can drastically change the output. In this example, the id_center is filtered before the JOIN is made. If it were in the WHERE clause, the filtering would be applied afterwards.

Upvotes: 4

Related Questions