CODEWITHSUNDEEP
mysql
Mircea Popescu
Mircea Popescu

Reputation: 43

How to filter not null values in mysql query?

I have the following mysql query. Joins are done only on FKs.

select le.lexicalentryid,  def.senseid, def.definitionid, frwf.writtenForm, trdef.writtentext, frwf.wordformid, frwf.phoneticForm, le.partofspeech, le.lexiconid
from formrepresentation_wordform frwf 
left join wordform wf on wf.wordformid = frwf.wordformid
left join lexicalentry le on le.lexicalentryid = wf.lexicalentryid
left join sense se on se.lexicalentryid = le.lexicalentryid
left join definition def on def.senseid = se.senseid
left join textrepresentation_definition trdef on trdef.definitionid = def.definitionid
where frwf.languageidentifier like '%deu%'

I get the following results (I show only the first 4)

I would like to get rid of the null values. They appear because for the same definitionid I have a single written form. When this happens I know I have to take the missing words from other table so now, I have to clear off the nulls.

I tried to add to the where clause frwf.writtenForm is not null, but with no success (all the writtenform column became blank)

Upvotes: 0

Views: 75

Answers (2)

Mircea Popescu
Mircea Popescu

Reputation: 43

I have managed to create the query:

SELECT frl.writtenForm, frl.languageIdentifier, le.partOfSpeech, wf.idx, frw.phoneticForm, se._index, trd.writtenText
FROM FormRepresentation_Lemma frl
JOIN LexicalEntry le ON (frl.lemmaId = le.lemmaId)
JOIN WordForm wf ON (le.lexicalEntryId = wf.lexicalEntryId)
JOIN FormRepresentation_WordForm frw ON (frw.wordFormId = wf.wordFormId)
JOIN Sense se ON (le.lexicalEntryId = se.lexicalEntryId)
JOIN Definition de ON (se.senseId = de.senseId)
JOIN TextRepresentation_Definition trd ON (de.definitionId = trd.definitionId)
WHERE frl.languageidentifier like '%deu%'

It was this table that I didn't take into account FormRepresentation_Lemma that contains link entries for null values

Upvotes: 1

Zack Newsham
Zack Newsham

Reputation: 3002

you need an inner join

select le.lexicalentryid,  def.senseid, def.definitionid, frwf.writtenForm, trdef.writtentext, frwf.wordformid, frwf.phoneticForm, le.partofspeech, le.lexiconid
from formrepresentation_wordform frwf 
inner join wordform wf on wf.wordformid = frwf.wordformid
left join lexicalentry le on le.lexicalentryid = wf.lexicalentryid
left join sense se on se.lexicalentryid = le.lexicalentryid
left join definition def on def.senseid = se.senseid
left join textrepresentation_definition trdef on trdef.definitionid = def.definitionid
where frwf.languageidentifier like '%deu%'

and possibly the other joins too. Inner join states that both tables must have a matching entry to be fetched.

Upvotes: 0

Related Questions