Jump to content
684425

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

Recommended Posts

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.

Tables.png

Share this post


Link to post
Share on other sites
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

Tables.png

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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     |
+--------+-------+---------+-------+

 

Share this post


Link to post
Share on other sites

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;  

 

Share this post


Link to post
Share on other sites
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🙂

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.