query pointer please

**IBazz** · Feb 9, 2011 02:52

Hi,
confused with this, and I shouldn't be!

(I wonder if I should be using CASE at all)

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?

Code MySQL:

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)

bazz

**r937** · Feb 9, 2011 03:25

Originally Posted by IBazz

what should I be doing?

what results are you getting with the query you posted, and how are those results different from what you want?

**IBazz** · Feb 9, 2011 05:51

well, I have tried a new query but I'll post the results for both it and the previous one I posted.

new query

Code MySQL:

SELECT coalesce(page_block_name)
, coalesce(sequence_number)
FROM `page_blocks_set` 
WHERE business_id = 477
and page_category in ('pages', 'default')

returns

Code:

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:-

Code MySQL:

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

Code:

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.

**IBazz** · Feb 9, 2011 06:02

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).

Code:

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

**IBazz** · Feb 9, 2011 06:07

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?

**r937** · Feb 9, 2011 06:33

you've lost me with your queries and result sets, i don't know what's what

by the way, COALESCE should really take at least two parameters (it can take many), but this --

Code:

SELECT coalesce(page_block_name)
     , coalesce(sequence_number)

... doesn't make a lot of sense

could you please give me the query again that you're having trouble with, and its results

**IBazz** · Feb 9, 2011 07:21

Thanks rudy,

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.

Code MySQL:

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

Code:

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

**r937** · Feb 9, 2011 07:27

Code:

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

**IBazz** · Feb 9, 2011 07:52

whoops? lol

I wonder if you are testing me again

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.

dat right?

thanks again

bazz

**r937** · Feb 9, 2011 08:17

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

User Tag List

Thread: query pointer please

Thread Tools

Display

query pointer please

Bookmarks

Bookmarks

Posting Permissions