Select row if other row is empty

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
shiznatix
DevNet Master
Posts: 2745
Joined: Tue Dec 28, 2004 5:57 pm
Location: Tallinn, Estonia
Contact:

Select row if other row is empty

Post 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?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Code: Select all

...AND l.Id IS NULL
User avatar
shiznatix
DevNet Master
Posts: 2745
Joined: Tue Dec 28, 2004 5:57 pm
Location: Tallinn, Estonia
Contact:

Post 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.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post 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?
User avatar
shiznatix
DevNet Master
Posts: 2745
Joined: Tue Dec 28, 2004 5:57 pm
Location: Tallinn, Estonia
Contact:

Post by shiznatix »

blah nevermind. I went another route and now I dont need that functionality anymore. thanks though.
Post Reply