Get default values, unless the foreign key is present in the table?

[maxxd]

Hey y'all. I'm having a bit of brain trouble here in that this seems like it should be really simple, but I'm just ... not coming up with the right query. Any help would be greatly appreciated.

 

Basically, I've got three featured image 'zones' on each of the pages in my site. Each of the zones has a variable number of absolutely-placed images, stacked one on top of the other. Once the images are in place, my JavaScript takes over and cycles the opacity of each of the images in each of the zones in order from top to bottom, in effect rotating the images. That part I have working nicely, all the way around.

 

However - and here's where my brain starts to stick - I want the site admin to be able to set site-wide default images for each zone, as well as page-specific images for each zone. So basically, if the page ID is in the featured images table, I want to select those images associated with that page ID. Otherwise, I want to select the images where page ID is null.

 

I'm embarrassed to say that this is as far as I can get right now on it:

SELECT	 fi.loc
	,fi.title
	,fi.alt
	,fi.zone
FROM tbl_featured_images fi
WHERE fi.pageID = :page
	OR fi.pageID IS NULL
ORDER BY fi.zone

This isn't giving me the results I want (for obvious reasons). I feel like the answer's on the tip of my brain, I just can't quite seem to grasp it. Any suggestions?

 

Many thanks in advance!

[Psycho]

If the value truly is NULL, I don't see an easy solution. You could create a sub-query that returns either the target ID (if it exists) or NULL. But, you wouldn't be able to use the result of that sub-query because you compare against a value and NULL differently. E.g.

 

SELECT *
FROM tbl_featured_images
WHERE pageID = 
(
    SELECT pageID
    FROM tbl_featured_images
    WHERE pageID = :page
       OR pageID IS NULL
    ORDER BY pageID ASC
    LIMIT 1
)

 

That sub-query will return either the page ID (if it exists for any records) else it returns NULL. The problem is you can't do a pageID = NULL

 

If you are using an empty string instead of NULL, then you can use the above and change the OR condition to look for that empty string and it will work. Even with NULL, this can probably be done with one query - but it may be rather complicated. May just be worth running one query to find records with the target ID. If the results are empty, then run a second query for all records that are NULL.

[Barand]

try

SELECT * 
FROM tbl_featured_images
WHERE (page_id = :page)
UNION
SELECT *
FROM tbl_featured_images
WHERE NOT EXISTS (SELECT * FROM imagetest WHERE page_id = :page)
AND page_id IS NULL;

Edited June 2, 2015 by Barand

[maxxd]

Barand, Psycho - thanks so much! Looking at both suggestions together got me to where I needed to be. I ended up with this:

SELECT * 
FROM tbl_feature_images
WHERE (pg_id = :page)
UNION
SELECT *
FROM tbl_feature_images
	WHERE NOT EXISTS (SELECT *
			  FROM tbl_feature_images
			  WHERE pg_id = :page)
AND pg_id IS NULL
UNION
SELECT *
FROM tbl_feature_images
WHERE pg_id IS NULL
	AND display_order NOT IN (SELECT display_order
				  FROM tbl_feature_images
				  WHERE pg_id = :page)
ORDER BY zone, display_order;

which lets my user replace specific images in the rotation on a per-page basis. Y'all rock the hizzy - I would say 'I knew it was something easy', but it wasn't. Thank you much!