CODEWITHSUNDEEP
mysqlsql
Thomas Sultana
Thomas Sultana

Reputation: 113

Mysql Case with multiple left outer joins

Im experimenting with the following idea. I am trying to build a fallback system all in the single query. Here is some background information:

posts_table
id, timestamp

posts_content_table
id, post_id, language_id, title, description

The goal here is to query posts_table then in the same query, join the posts_content_table using a fallback system. So if i search for post content with a language_id that doesn't exist it should be able to fallback to an language_id that does contain content. It should allow for multiple levels of fallbacks.

Example:

SELECT p.id FROM posts_table AS p 

LEFT OUTER JOIN posts_content_table AS pc ON (p.id=pc.post_id AND pc.language_id=1)
LEFT OUTER JOIN posts_content_table AS pc2 ON (p.id=pc2.post_id AND pc2.language_id=2)
LEFT OUTER JOIN posts_content_table AS pc3 ON (p.id=pc3.post_id AND pc3.language_id=3)

Naturally what will happen is when it returns data which is not null, that data should be used as the final result. So this brings me to CASES. How would i implement CASES to work in this way? Would the following suffice?

SELECT p.id, CASE title
WHEN pc.title is Null THEN pc2.title
WHEN pc2.title is Null THEN ps3.title
WHEN pc3.title is Null THEN '-'
END
FROM posts_table AS p 

LEFT OUTER JOIN posts_content_table AS pc ON (p.id=pc.post_id AND pc.language_id=1)
LEFT OUTER JOIN posts_content_table AS pc ON (p.id=pc.post_id AND pc.language_id=2)
LEFT OUTER JOIN posts_content_table AS pc ON (p.id=pc.post_id AND pc.language_id=3)

Upvotes: 2

Views: 1961

Answers (1)

Michael Berkowski
Michael Berkowski

Reputation: 270727

This is the purpose of COALESCE(), which returns the first non-null of its arguments:

SELECT 
  p.id, 
  COALESCE(pc.title, pc2.title, pc3.title, '-') AS title
FROM posts_table AS p 
LEFT OUTER JOIN posts_content_table AS pc ON (p.id=pc.post_id AND pc.language_id=1)
LEFT OUTER JOIN posts_content_table AS pc2 ON (p.id=pc2.post_id AND pc2.language_id=2)
LEFT OUTER JOIN posts_content_table AS pc3 ON (p.id=pc3.post_id AND pc3.language_id=3)

(Note: I fixed your table aliases - they were all pc and I assume you meant to alias as pc, pc2, pc3)

Upvotes: 3

Related Questions