Random records from one table based on a row from other table

[684425]

From tables shown in image, I am trying to run a query for employee 67 which return only one row containing id, pay from employee table and from overtime table i want to choose values from columns hours1, hours2, hours3 based on references mentioned in employees table. I have highlighted the values in both tables for which i want to run the query.

[684425]

SELECT
    tbl1.pay AS bpay,
    tbl2.grade AS t1,
    tbl3.grade AS t2,
    tbl4.grade AS t3,
    tbl5.hours1 AS d1,
    tbl6.hours2 AS d2,
    tbl7.hours3 AS d3
FROM
    employees AS tbl1
LEFT JOIN employees AS tbl2
ON
    tbl1.time1 = tbl2.id
LEFT JOIN employees AS tbl3
ON
    tbl1.time2 = tbl3.id
LEFT JOIN employees AS tbl4
ON
    tbl1.time3 = tbl4.id
LEFT JOIN overtimes AS tbl5
ON
    tbl2.grade = tbl5.ranks
LEFT JOIN overtimes AS tbl6
ON
    tbl3.grade = tbl6.ranks
LEFT JOIN overtimes AS tbl7
ON
    tbl4.grade = tbl7.ranks
WHERE
    tbl1.id = 67

The above query returns result including red highlighted values which I am trying to remove

[benanamen]

You first need to fix your DB design. Consecutive numbered columns is a red flag your DB is wrong. Look up "Database Normalization" and then fix the DB.

[Barand]

Or just use Excel instead of trying to store spreadsheet tables in a database. I agree with @benanamen - normalize. Something like this

+------------+          
| employee   |          
+------------+          +------------+ 
|  emp_id    |----+     |  time      | 
|  fname     |    |     +------------+  
|  lname     |    |     | time_no    |----+ 
|  pay       |    +----<| emp_id     |    |
|  grade     |--+       | time_emp   |    |
+------------+  |       +------------+    |
                |                         |
                |       +------------+    |
                |       |  overtime  |    |
                |       +------------+    |
                +------<| grade      |    |
                        | time_no    |>---+
                        | hours      |
                        +------------+

Your table data

employee                         time                                  overtime
+--------+-------+-------+       +---------+--------+----------+       +-------+-------+---------+-------+
| emp_id | pay   | grade |       | time_no | emp_id | time_emp |       | ot_id | grade | time_no | hours |
+--------+-------+-------+       +---------+--------+----------+       +-------+-------+---------+-------+
| 11     | 12500 | 3     |       | 1       | 11     | 6        |       | 1     | 3     | 1       | 10    |
| 15     | 15000 | 5     |       | 1       | 15     | 4        |       | 2     | 3     | 2       | 5     |
| 23     | 17000 | 7     |       | 1       | 23     | 15       |       | 3     | 3     | 3       | 0     |
| 67     | 20000 | 9     |       | 1       | 67     | 23       |       | 4     | 5     | 1       | 15    |
+--------+-------+-------+       | 2       | 11     | 2        |       | 5     | 5     | 2       | 10    |
                                 | 2       | 15     | 23       |       | 6     | 5     | 3       | 5     |
                                 | 2       | 23     | 8        |       | 7     | 7     | 1       | 20    |
                                 | 2       | 67     | 15       |       | 8     | 7     | 2       | 15    |
                                 | 3       | 11     | 19       |       | 9     | 7     | 3       | 10    |
                                 | 3       | 15     | 10       |       | 10    | 9     | 1       | 25    |
                                 | 3       | 23     | 11       |       | 11    | 9     | 2       | 20    |
                                 | 3       | 67     | 11       |       | 12    | 9     | 3       | 15    |
                                 +---------+--------+----------+       +-------+-------+---------+-------+

Then

SELECT
       e.emp_id
     , e.pay
     , t.time_no
     , ot.hours
FROM employee e 
       JOIN
     time t ON e.emp_id = t.emp_id
       JOIN 
     employee e2 ON t.time_emp = e2.emp_id
       JOIN
     overtime ot ON e2.grade = ot.grade AND t.time_no = ot.time_no
WHERE e.emp_id = 67
ORDER BY time_no;

+--------+-------+---------+-------+
| emp_id | pay   | time_no | hours |
+--------+-------+---------+-------+
| 67     | 20000 | 1       | 20    |
| 67     | 20000 | 2       | 10    |
| 67     | 20000 | 3       | 0     |
+--------+-------+---------+-------+

 

[Barand]

If you don't want to take our advice, you could resort to 

SELECT h1.emp_id
     , h1.pay
     , h1.hours1
     , h2.hours2
     , h3.hours3;
SELECT *
FROM 
    (
        SELECT e.emp_id
             , e.pay
             , ot.hours1 as hours
        FROM employeex e
             JOIN
             employeex e2 ON e.time1 = e2.emp_id
             JOIN
             overtimex ot ON e2.grade = ot.rank
    ) h1
    JOIN
     (
        SELECT e.emp_id
             , e.pay
             , ot.hours2
        FROM employeex e
             JOIN
             employeex e2 ON e.time2 = e2.emp_id
             JOIN
             overtimex ot ON e2.grade = ot.rank
    ) h2 ON h1.emp_id = h2.emp_id
    JOIN
     (
        SELECT e.emp_id
             , e.pay
             , ot.hours3
        FROM employeex e
             JOIN
             employeex e2 ON e.time3 = e2.emp_id
             JOIN
             overtimex ot ON e2.grade = ot.rank
    ) h3 ON h1.emp_id = h3.emp_id
 WHERE h1.emp_id = 67;  

 

[684425]

7 hours ago, Barand said:

If you don't want to take our advice, you could resort to 

SELECT h1.emp_id
     , h1.pay
     , h1.hours1
     , h2.hours2
     , h3.hours3;
SELECT *
FROM 
    (
        SELECT e.emp_id
             , e.pay
             , ot.hours1 as hours
        FROM employeex e
             JOIN
             employeex e2 ON e.time1 = e2.emp_id
             JOIN
             overtimex ot ON e2.grade = ot.rank
    ) h1
    JOIN
     (
        SELECT e.emp_id
             , e.pay
             , ot.hours2
        FROM employeex e
             JOIN
             employeex e2 ON e.time2 = e2.emp_id
             JOIN
             overtimex ot ON e2.grade = ot.rank
    ) h2 ON h1.emp_id = h2.emp_id
    JOIN
     (
        SELECT e.emp_id
             , e.pay
             , ot.hours3
        FROM employeex e
             JOIN
             employeex e2 ON e.time3 = e2.emp_id
             JOIN
             overtimex ot ON e2.grade = ot.rank
    ) h3 ON h1.emp_id = h3.emp_id
 WHERE h1.emp_id = 67;  

 

Sir, I am following your advice. I have normalized my database and now I am working on app to remove errors that is why my reply is late🙂