How to fix query results not showing up?

Question

I have a MySQL query which I want to execute to see who is the employee with the best skill X in a company I work for. To do this I randomly pick a company from my cv_profile (skill_cv_test) and find all users who work there for the same employer. And then I randomly choose a skill I have. The result should either be zero or a list. But when testing with PHPMyAdmin I get results where I don't see any row, but the status says there is at least one row. Here's an example of the message I get: https://i.stack.imgur.com/vUKUW.jpg

I have been trying different structures, even "walling" the query with another query, different joins.

SELECT
DISTINCT(sv.usr_id),
u.first_name AS fn,
u.last_name AS ln,
c.name AS company,
s.name AS skill 
FROM 
    (
        SELECT 
            MAX(last_change) as date, 
            id,
            usr_id, 
            skill_id
        FROM skill_valuations 
        GROUP BY usr_id, skill_id
        ORDER BY date
    ) sv
LEFT JOIN skill_valuations skv ON skv.last_change = sv.date
INNER JOIN 
    (
        SELECT 
            DISTINCT(skct.comp_id),
            skct.usr_id AS usr_id, 
            skct.category 
        FROM skill_cv_test skct 
        WHERE skct.end_date IS NULL AND skct.comp_id IN (SELECT comp_id FROM (SELECT comp_id FROM skill_cv_test WHERE usr_id = 1 ORDER BY RAND() LIMIT 1) x)
    ) uqv ON uqv.usr_id = sv.usr_id
INNER JOIN 
    (
        SELECT skill_id 
        FROM usr_skills 
        WHERE usr_id = $uid 
        ORDER BY RAND() 
        LIMIT 1
    ) usq ON usq.skill_id = sv.skill_id 

LEFT JOIN companies c ON c.id = uqv.comp_id
LEFT JOIN skills s ON s.id = sv.skill_id
LEFT JOIN users u ON u.id = sv.usr_id

As mentioned before, I expect either no results or a result of at least one row.

MySQL 's DISTINCT is a keyword not a function it will still count for all columns.. if you want to use a DISTINCT as function use PostgreSQL where `DISTINCT ON(), *` can be uses to unduplicate one column.. — Raymond Nijland, Oct 02 '19 at 15:38
in MySQL you need to do more or less `SELECT ... WHERE (, usr_id) IN ( SELECT , MIN/MAX(usr_id) ... GROUP BY ... )` ... offcource there more options like using ROW_NUMBER of a self LEFT JOIN with a shifting filter where the MIN/MAX also can be found.. Or you emulate `DISTINCT ON(), *` in MySQL with [GROUP_CONCAT (post off mine)](https://stackoverflow.com/questions/53868870/how-to-use-distinct-on-with-mysql-using-activerecord/53869691#53869691) — Raymond Nijland, Oct 02 '19 at 15:43

How to fix query results not showing up?

0 Answers0