Page 1 of 1

Select row if other row is empty

Posted: Mon Aug 21, 2006 12:15 am
by shiznatix
I have a table that holds 'additional information'. The user can put in a row for each language available.

What I want is that if someone is browsing in French and they go to the page they view the 'additional information' in french. That works fine but...

I want that if there is no additional information for that page in french then it to just default to english or whatever other language I have set as default. How do I do that in 1 query? Right now I just have:

Code: Select all

SELECT
  lai.Information
FROM
  listing_additional_information AS lai
LEFT JOIN
  languages AS l
ON
  l.Id = lai.fkLanguageId
WHERE
  lai.fkListingId = '.$Id.'
AND
  l.Abbreviation = 'LANGUAGE_CODE'
I want to add something like:

IF lai.Information for language abbreviation 'LANGUAGE_CODE' is empty, select where l.Abbreviation = 'DEFAULT_LANGUAGE_ABBREVIATION'

How is this done?

Posted: Mon Aug 21, 2006 12:21 am
by feyd

Code: Select all

...AND l.Id IS NULL

Posted: Mon Aug 21, 2006 1:19 am
by shiznatix
feyd wrote:

Code: Select all

...AND l.Id IS NULL
I don't see how that would work at all. l.Id is never null.

There is always an entry for the language id, just the 'Information' field might just contain an empty string.

Posted: Mon Aug 21, 2006 1:28 am
by Weirdan
ifnull(some.id) is a way to go

You may need to reverse your join though (and add one more join to get the lai row for default language).

btw, what's the structure of these tables?

Posted: Mon Aug 21, 2006 1:30 am
by shiznatix
blah nevermind. I went another route and now I dont need that functionality anymore. thanks though.