Mysql – Getting rows in left table only if not present in the right table

join;MySQL

I need to get only those rows which dont have the corresponding entry in the right table advlabel , so I am doing a full left outer join :

SELECT newparsed.seqid,newparsed.wordindex,label,arg1,arg2,sublabel FROM newparsed
LEFT OUTER JOIN   advlabel ON ((`advlabel`.`seqid` = `newparsed`.`seqid`) AND (`advlabel`.`wordindex` = `newparsed`.`wordindex`))
WHERE sublabel = NULL

So is the above query correct sublabel is a column from the right side table advlabel ?

both tables have primarykey(seqid,wordindex)

Best Answer

Here is an example :

SELECT 
    a.col1
    , a.col2
    , a.col3 
FROM 
   table1 a
   LEFT JOIN table2 b 
       ON 
           (a.pkcol = b.pkcol)
WHERE 
   b.pkcol IS NULL

So in your case, it will most likely be :

SELECT 
    n.seqid
    ,n.wordindex
    ,n.label
    ,n.arg1
    ,n.arg2
    ,n.sublabel 
FROM 
    newparsed n
        LEFT JOIN   advlabel a 
            ON 
                ((a.`seqid` = n.`seqid`) 
            AND 
                (a.`wordindex` = n.`wordindex`))
WHERE 
   a.`seqid` IS NULL

You just have to use LEFT JOIN and select any field (I suggest a primary key) in the right table that is NULL

Related Solutions

Mysql – Getting Skewed Results from LEFT OUTER JOIN with 3 TABLES

JOINs together with aggregates (eg, SUM()) often give you bigger values than you expect. To see this, remove the SUMs and GROUP BY and stare at the output. You may see, for example, the same mghours.hourstime showing up multiple times. Hence, SUM(mghours.hourstime) is inflated.

This is happening because JOIN mgtraining is coming up with multiple rows in mgtraining for each row in mghours.

The solution is to do the query in steps. Notice how the inner query avoids inflating "vol":

SELECT  x.*,
        SUM(...) AS 'at
    FROM (
        SELECT  ...,
                SUM(mghours.hourstime) AS vol
            FROM ...
            GROUP BY ...
         ) x
    LEFT JOIN mgtraining ON ...
    GROUP BY ...
    ORDER BY ...;

PostgreSQL – Left Join Not Working with Sub-query

You could use a CROSS JOIN from score_sort to all the students and then a LEFT join to score:

SELECT st.student_id, 
       AVG(COALESCE(sc.value, default_value)) AS average_score
FROM 
(
    SELECT score.subject_id, subject.name, SUM(score.value) AS score_sum
    FROM score
    JOIN subject on subject.id = score.subject_id
    WHERE subject.name != 'skip me'
    GROUP BY score.subject_id
    ORDER BY score_sum DESC
    LIMIT 5
) AS score_sort AS sort 
  CROSS JOIN student AS st
  LEFT JOIN score AS sc
    ON  sort.subject_id = sc.subject_id 
    AND   st.student_id = sc.student_id
GROUP BY st.student_id ;

A different approach would be to alter your query to count the available scores each student has (in the 5 top subjects):

SELECT score.student_id, 
       (SUM(score.value) + (5 - COUNT(score.subject_id)) * default_value) / 5 
           AS average_score
FROM 
(
    SELECT score.subject_id, subject.name, SUM(score.value) AS score_sum
    FROM score
    JOIN subject on subject.id = score.subject_id
    WHERE subject.name != 'skip me'
    GROUP BY score.subject_id
    ORDER BY score_sum DESC
    LIMIT 5
) AS score_sort
JOIN score ON score_sort.subject_id = score.subject_id 
GROUP BY score.student_id ;

But even with this, you would still not get any result for the students that have no score in any of the 5 top subjects.

Best Answer

Related Solutions

Mysql – Getting Skewed Results from LEFT OUTER JOIN with 3 TABLES

PostgreSQL – Left Join Not Working with Sub-query

Related Question