Using an IF in a Mysql query

Question

I have two tables with milions of records that I need to join to display results in different languages. I wish to create a view using a query with IF conditions but I'm not able to understand which is the right way.

What I need to do is: if the following select has a NULL for a single row (in other words the row doesn't have a deutsch translation)...

SELECT one, two FROM tableA A LEFT JOIN tableB B ON (A.id=B.id) WHERE language='de' order by xyz

...then take the english version for the same row and so do this select:

SELECT one, two FROM tableA A LEFT JOIN tableB B ON (A.id=B.id) WHERE language='en' order by xyz

Thanks! Luca

I don't know MySql specifically, but does it have a @@rowcount feature? — iGanja
– iGanja, Commented Apr 25, 2013 at 16:21

Gordon Linoff · Accepted Answer · 2013-04-25 18:02:38Z

1

You query is hard to follow because you don't have aliases. I am assuming that it is structured like this:

select a.one. b.two
from tableA a left join
     tableb b
     on a.id = b.id
where b.language = 'de'
order by xyz

The following provides logic that should work in MySQL:

SELECT a.one, 
       (case when (select COUNT(*) from b where b.id = a.id and b.language = 'de' and b.two is null) = 0
             then bde.two
             else bend.two
        end) as two
FROM tableA A LEFT JOIN
     tableB Bde
     ON (A.id=B.id) and
        bde.language='de' left join
     tableB ben
     on ben.language = 'en'
order by xyz

This is assuming that the NULL value is stored in the B table. If it is lost through a join, then it is a bit harder, something like:

SELECT a.one, 
       (case when (select COUNT(*) from a a2 join b b2 on a2.id = b2.id and b2.language = 'de' and b2.two is null) = 0
             then bde.two
             else bend.two
        end) as two
FROM tableA A LEFT JOIN
     tableB Bde
     ON (A.id=B.id) and
        bde.language='de' left join
     tableB ben
     on ben.language = 'en'
order by xyz

edited Apr 25, 2013 at 18:02

answered Apr 25, 2013 at 16:39

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

1 Comment

Luca Over a year ago

Thank you really for your help. I changed the query in this way

SELECT  g.geonameid, alternateName, latitude, longitude, fclass, fcode, country FROM geoname g LEFT JOIN alternatename a ON (g.geonameid=a.geonameid) WHERE IF(select COUNT(*) from geoname g JOIN alternatename a ON g.geonameid=a.geonameid and isoLanguage='de', 1, 0)=1 THEN isolanguage='de' ELSE isolanguage='en' order by isPreferredName

but I receive an error near WHERE if(select... I tried your query but whitout success.

Collectives™ on Stack Overflow

Using an IF in a Mysql query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related