CODEWITHSUNDEEP
mysql
Tino Fourie
Tino Fourie

Reputation: 59

Combine Two MySQL Tables Without Duplication (No previous topic provided solution)

I am at the edge of my sanity here. I have looked around at possible solutions before posting my problem, and I could not find any to solve my problem.

I have three tables with the following layout:

     Employee                User_logs                        Assessment
|----------------| |----------------------|-------| |----------------------|-------|
| empID  | uName | | checkindate | status | FK_ID | | asDate     |saResult | FK_ID |
|----------------| |----------------------|-------| |----------------------|-------|
| 1      | Tino  | | 2020-04-04  | Clear  |   1   | | 2020-04-04 | Pass    |   1   |
| ...    | ...   | | 2020-05-05  | Fail   |   1   | | 2020-05-05 | Pass    |   1   |
| ...    | ...   | | 2020-07-07  | Fail   |   1   | | 2020-06-06 | Fail    |   1   |
| ...    | ...   | | 2020-08-08  | Clear  |   1   | | 2020-08-08 | Pass    |   1   |
| ...    | ...   | |------------------------------| | 2020-09-09 | Pass    |   1   |
|----------------|                                  |------------------------------|

empID is a Primary Key I use as a Foreign Key in tables User_Logs and Assessment. The desired result I am looking for is as follows:

                    Result
| FK_ID  |   resultDate | Status| Result |
|----------------------------------------|
|   1    | 2020-04-04   | Clear | Pass   |
|   1    | 2020-05-05   | Fail  | Pass   |
|   1    | 2020-06-06   |       | Fail   |
|   1    | 2020-07-07   | Fail  |        |
|   1    | 2020-08-08   | Clear | Pass   |
|   1    | 2020-09-09   |       | Pass   |  <---- Not shown
|----------------------------------------|

I do get close with the following code, however for some reason it does not pick up on the very last entry of 2020-09-09:

SELECT A.checkindate, A.status, B.saDate, B.saResult
FROM myDatabase.User_logs AS A 
      LEFT JOIN (
        SELECT DISTINCT saDate, saResult, FK_empID
        FROM myDatabase.assesment
      ) AS B 
      ON  B.FK_empID = 1
      AND B.saDate = A.checkindate
WHERE A.FK_empID = 1
ORDER BY A.checkindate DESC

What am I doing wrong or not seeing ?

Upvotes: 0

Views: 50

Answers (2)

Caius Jard
Caius Jard

Reputation: 74660

Have a go at refining this:

SELECT * FROM
  (
    SELECT fk_id, checkInDate as d FROM user_logs WHERE fk_id = 1 
    UNION 
    SELECT fk_id, asDate FROM assessment WHERE fk_id = 1
  ) datelist

  LEFT JOIN user_logs ul 
  ON 
    datelist.d = ul.checkindate AND 
    datelist.fk_id = ul.fk_id

  LEFT JOIN assessment a 
  ON 
    datelist.d = a.asdate AND 
    datelist.fk_id = a.fk_id

The basic concept is we produce a unique list of dates for employee 1, onto which we join the logs and the assessments. These joins will always work out in one case or the other (maybe both), by definition, because the things we are joining on came from the tables originally

I've left the join to Employee out of the query for clarity - I'm trying to explain to you why this works and yours doesnt - you can inner join the Employee table onto the datelist, and arrange for the columns you want in the SELECT

In terms of the original query, it was logs left join assessment - this means because there is no 2020-09-09 value in logs, the 2020-09-09 rows from assessment is just tossed out; essentially when doing a left join you say to the DB, "here is all the info I'm mainly interested in on the left, and also I'd like to know if anything from the right matches to it" - you told the db you weren't interested in the 2020-09-09 row implicitly as a result

You could flip it around, so that assessment is "mainly what you're interested in" and then show logs only if they match up, but then you might get the same problem with assessment not having a row that you want from logs

FULL OUTER JOIN is often used for things like this - youre saying with that "i'm equally interested in these things on both sides, and if it has a match on the other side, great.. If not, I'm not gonna be upset"

The technique presented in this answer is a little different; we build a unique list of dates that is definitely all encompassing (it has all the logs dates and all the assessment dates, then duplicates removed) so you can then claim "this is definitely 100% the list of dates I'm interested in, and if there is a logs, great, if there's an assessment, great, if not in either case, it's ok too"

Upvotes: 1

Akina
Akina

Reputation: 42728

SELECT e.empID, d.resultDate, u.Status, a.saResult Result 
FROM Employee e
CROSS JOIN ( SELECT checkindate resultDate
             FROM User_logs
           UNION 
             SELECT asDate
             FROM Assessment ) d
LEFT JOIN User_logs u ON e.empID = u.FK_ID AND d.resultDate = u.checkindate
LEFT JOIN Assessment a ON e.empID = a.FK_ID AND d.resultDate = a.asDate
-- WHERE COALESCE(u.Status, a.saResult)
-- ORDER BY empID, resultDate

Upvotes: 2

Related Questions