How to filter not null values in mysql query?

Question

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)

the odds i would run into you here have got to be astronomical — r3wt
– r3wt, Commented Nov 30, 2014 at 23:29

Mircea Popescu · Accepted Answer · 2014-12-01 10:17:03Z

1

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

answered Dec 1, 2014 at 10:17

Mircea Popescu

435 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Zack Newsham · Accepted Answer · 2014-11-30 23:53:33Z

0

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.

answered Nov 30, 2014 at 23:53

Zack Newsham

3,0022 gold badges27 silver badges45 bronze badges

2 Comments

Mircea Popescu Over a year ago

It's not working. Probably due to the fact that I don't join on the written form. all the columns I join on columns have matching entries.

Zack Newsham Over a year ago

So each table has a matching entry, then you have null values saved somewhere?

Collectives™ on Stack Overflow

How to filter not null values in mysql query?

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related