mysql select row based in many column values

Question

I have a table like this

id alias_word language  original
1    word1      es      changed_word
2    word1      en      orig_word
3    word1      fr      changed_word
4    word2      de      other_original

Supposing reference column language = es

How to make a query to have result all rows that have alias_word column = word1 and the original column is different than original column where language column = es

espected result:

2 word1 en orig_word

Ihave tried this and have empty result

SELECT * FROM words WHERE alias_word = 'word1' AND original <> original

Tim Biegeleisen · Accepted Answer · 2018-03-19 03:31:48Z

1

Try using a self join:

SELECT w2.*
FROM words w1
INNER JOIN words w2
    ON w1.alias_word = w2.alias_word AND
       w1.original <> w2.original
WHERE
    w1.language = 'es';

Demo

edited Mar 19, 2018 at 3:31

answered Mar 19, 2018 at 3:26

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

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

1 Comment

MTK Over a year ago

Ok. thank's ... It work. I have added WHERE w1.language = 'es' AND w1.alias = 'my_wanted_alias'; to adapt at my needs

LordBaconPants · Accepted Answer · 2018-03-19 03:29:58Z

0

AND original <> original Will always return nothing. I don't quite follow what you are doing with that original column, but I think you want to not have the word 'original' in the original column:

SELECT * 
FROM words 
WHERE alias_word = 'word1' 
    AND original <> 'original'
    AND language = 'es'

edited Mar 19, 2018 at 3:29

answered Mar 19, 2018 at 3:24

LordBaconPants

1,4241 gold badge20 silver badges23 bronze badges

2 Comments

Tim Biegeleisen Over a year ago

This works for the particular example in the OP, but does so by hard coding values.

MTK Over a year ago

Here something not ok ... AND original <> 'original' ... I can't know the value of original ... this must be something AND original <> 'I_need_this_value_by_one_query_where_language_es'

Collectives™ on Stack Overflow

mysql select row based in many column values

2 Answers 2

Demo

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related