/    Sign up×
Community /Pin to ProfileBookmark

Multi table Join displaying incorrect data

Copy linkTweet thisAlerts:
Sep 22.2020

db fiddle: https://www.db-fiddle.com/f/qKDzqVNxVHsMcnPd2JpuWM/8

I have a table of technicians: `Tech` which is simply customer_id, tech, classification, allocation

I want to display customer_id, tech, **name**, classification, allocation

Problem is that **name** comes from ReportData (Reports have an entry for the current day PLUS every month end going back as long as they have been sending data )

I want to get the most recent name in the ReportData table to display alongside the **tech** (Worst case, just get names from the most recent active report and show null for ids not in current report)

BUT:

  • 1. **tech** is non-unique and can be used for other customers (so need to filter **tech** by **customer_id**)

  • 2. **customer_id** is not on the ReportData table but is in the header table ReportHeader (So requires a multi JOIN)

    • I tried to use JOINs to get this data but it is not working as I expected.

    See 7599 – It is showing the employee from 2017 – I’m guessing there has to be a better way to do this?

    to post a comment

    3 Comments(s) ↴

    Copy linkTweet thisAlerts:
    @NogDogSep 22.2020 — > @php-bgrader#1623516 I want to get the most recent name in the ReportData

    That might be easier to do via a sub-query, perhaps?
    <i>
    </i>select &lt;various fields&gt;, (select name from ReportData where &lt;something&gt;) as report_name
    from &lt;etc....&gt;
    Copy linkTweet thisAlerts:
    @NogDogSep 22.2020 — PS: I should have mentioned that the idea would then be to include something in the sub-query where you could order by some field so that the "latest" one appears first, then add a "limit 1" so that you just get the latest value.
    Copy linkTweet thisAlerts:
    @php-bgraderauthorSep 22.2020 — @NogDog#1623543 Thanks. I've decided to try a different approach.

    Every time I get a file I'm going to add a name column to Tech and do an INSERT ON DUPLICATE UPDATE on the Tech table and then remove the lookup altogether.
    ×

    Success!

    Help @php-bgrader spread the word by sharing this article on Twitter...

    Tweet This
    Sign in
    Forgot password?
    Sign in with TwitchSign in with GithubCreate Account
    about: ({
    version: 0.1.9 — BETA 4.23,
    whats_new: community page,
    up_next: more Davinci•003 tasks,
    coming_soon: events calendar,
    social: @webDeveloperHQ
    });

    legal: ({
    terms: of use,
    privacy: policy
    });
    changelog: (
    version: 0.1.9,
    notes: added community page

    version: 0.1.8,
    notes: added Davinci•003

    version: 0.1.7,
    notes: upvote answers to bounties

    version: 0.1.6,
    notes: article editor refresh
    )...
    recent_tips: (
    tipper: @Yussuf4331,
    tipped: article
    amount: 1000 SATS,

    tipper: @darkwebsites540,
    tipped: article
    amount: 10 SATS,

    tipper: @Samric24,
    tipped: article
    amount: 1000 SATS,
    )...