KDJ
KDJ

Reputation: 309

Joining 3 tables in SQL table with COALESCE returning no results

I've got the following query that keeps returning no results. All the table names and columns are correct; however, cannot get it to return results. I take out the themes table and it works correctly. Something isn't working right with the themes section. Can anyone spot the issue? The table and column names are correct. Its my first time using COALESCE and I'm not sure if I'm using it correctly.

Dealing with themes, here is my goal:

if draft_pages.theme = 0, I want it to pull the value from domain_profile.default_theme instead. Not sure if COALESCE is the right solution or not.

$getDRAFTinfo = $con->query("
SELECT 
draft_pages.title, 
draft_pages.url, 
draft_pages.status, 
draft_pages.modified, 
draft_pages.modifier, 
draft_pages.created, 
draft_pages.creator, 
draft_pages.domains, 
COALESCE(draft_pages.theme, domain_profile.default_theme), 
draft_pages.meta, 
draft_pages.script, 
draft_pages.page_lock, 
draft_pages.design_lock, 
themes.stylesheets, 
domain_profile.default_theme 
FROM draft_pages 
JOIN domain_profile ON domain_profile.domain = '". $websitedomain ."' 
JOIN themes ON themes.id = COALESCE(draft_pages.theme, domain_profile.default_theme) 
WHERE draft_pages.id = '". $draftID ."'") 
or die ('Unable to execute query. '. mysqli_error($con));

Upvotes: 0

Views: 190

Answers (1)

Rahul
Rahul

Reputation: 77866

Use a CASE statement instead like

case when draft_pages.theme = 0 then domain_profile.default_theme else draft_pages.theme end

Upvotes: 1

Related Questions