I want to get a resultset of the two values (page_block_name, sequence_number), where the category is ‘pages’. if there are no records in that category I want only to return the values where the category is NULL.
what should I be doing?
select page_block_name
,sequence_number
,case when
page_category = 'pages' then 1
else 0
end
from page_blocks_set
where business_id = 477
and ( page_category = 'pages'
or page_category is NULL)
well, I have tried a new query but I’ll post the results for both it and the previous one I posted.
new query
SELECT coalesce(page_block_name)
, coalesce(sequence_number)
FROM `page_blocks_set`
WHERE business_id = 477
and page_category in ('pages', 'default')
returns
coalesce(page_block_name) coalesce(sequence_number)
accommodation quick booker 5
accommodation quick booker 0
address 29
address 0
banner 0
banner 0
close special div for thumbnail gallery 26
close special div for thumbnail gallery 0
close video content 26
close video content 0
comments div closing tag 26
comments div closing tag 0
comments div opening tag 22
comments div opening tag 0
This seems to bring back what I want but unfortunately, it also brings back the other ‘default’ values when they are not wanted. I only want the default values if there are no values where the page_category matches the ‘search term’ of pages.
From the earlier query:-
SELECT page_block_name
,sequence_number
,CASE WHEN
page_category = 'pages' THEN 1
ELSE 0
END
FROM page_blocks_set
WHERE business_id = 477
AND ( page_category = 'pages'
OR page_category = default)
returns
page_block_name sequence_number CASE RESULTS (col title abbreviated)
close special div for thumbnail gallery 00 1
close video content 00 1
comments div closing tag 00 1
comments div opening tag 00 1
comments photo list 00 1
confab div closing tag 00 1
confab div opening tag 00 1
container closing tag 00 1
Brings back the correct page_block_name but without the sequence_number.
In reality I only need the sequence number to be used for sorting the result into the correct sequence. I don’t really need it to be retunred as a value to be used in my web page.
Oops, scrub the second query. I had somehow written DEFAULT when it should have been = ‘default’ It brings back similar resulst to the first coalesce query - ie both results but also, an extra col with the case number added.
the results from that query are as follows. why does the correct result have a case number of 0 when it should have 1 (I think).
page_block_name sequence_number CASE (title abbreviated)
accommodation quick booker 05 0
accommodation quick booker 00 1
address 29 0
address 00 1
banner 00 0
banner 00 1
close special div for thumbnail gallery 26 0
close special div for thumbnail gallery 00 1
just to clarify… when the category is ‘pages’, the CASE is returning 1 but with sequence number of 00. And when the page_category is ‘default’ it is returning 0 for the CASE. Why though, is the sequence_number showing as 0 when it is the resultset I want i.e., in the relevant resultset?
I posted a couple of queries because I didn’t know which was the way to go.
my table has two bundles of data, differentiated by the page_category. I want to return those in the category ‘pages’ but if there are none, to return those in the ‘default’ category. (either/or, never a mixture of page_category).
this query seems to return everything in both categories. I need a way to stop ‘default’ being returned if there are ‘pages’ results.
SELECT page_block_name
, CASE
when page_category = 'pages' then 1
when page_category = 'default' then 2
end as stuff
, page_category
, sequence_number
FROM page_blocks_set
WHERE business_id = 477
AND ( page_category = 'pages'
OR page_category = 'default' )
order by sequence_number
main content div tag 1 pages 00
food page photos 1 pages 00
job vacancies div opening tag 1 pages 00
container opening tag 2 default 01
logo 2 default 02
strapline 2 default 03
page divider one 2 default 04
SELECT page_block_name
, page_category
, sequence_number
FROM page_blocks_set
WHERE business_id = 477
AND page_category = 'pages'
UNION ALL
SELECT page_block_name
, page_category
, sequence_number
FROM page_blocks_set
WHERE business_id = 477
AND page_category = 'default'
AND NOT EXISTS
( SELECT 'whoops'
FROM page_blocks_set
WHERE business_id = 477
AND page_category = 'pages' )
ORDER
BY sequence_number
I think ‘whoops’ will always return null but if I substitute whoops with ‘page_block_name’, it will only be NULL if no ‘pages’ value exists in page_category.
in an EXISTS or NOT EXISTS subquery, it does not matter what you put into the SELECT clause, the only thing that matters is whether any rows are returned
