1

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)

5
  • 1
    No it isn't correct. Can you confirm whether sublabel is nullable or not please? Jul 13, 2016 at 9:29
  • 1
    Which field is in which table? And = NULL should be is NULL
    – piet.t
    Jul 13, 2016 at 9:30
  • This is not the goal of a LEFT OUTER JOIN :-)
    – Marco
    Jul 13, 2016 at 9:55
  • 1
    @Marco using a left outer join .... Is null to do an anti semi join is quite a common approach and according to this article performs as well as any other in MySql explainextended.com/2009/09/18/… Jul 13, 2016 at 10:02
  • Your question is a bit confusing, if both tables have same primary key then why are you using a third column "sublabel" that too is in both tables for querying. You can check if advlabel doesn't have record with primary keys i.e seqid, wordindex. Use Sub Query and it will work. Jul 13, 2016 at 10:45

3 Answers 3

3

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

0

Use the NOT IN option:

SELECT newparsed.seqid,newparsed.wordindex,label,arg1,arg2,sublabel
FROM   newparsed
where  (seqid, wordindex) NOT IN (SELECT seqid, wordindex FROM advlabel)
0
SELECT newparsed.seqid,newparsed.wordindex,label,arg1,arg2,sublabel    
FROM   newparsed as np      
WHERE NOT EXISTS  
  (SELECT seqid, wordindex
   FROM   advlabel as a
   WHERE  a.seqid     =np.seqid
   AND    np.wordindex=a.wordindex  )

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.