SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    query pointer please

    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

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by IBazz View Post
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  4. #4
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  5. #5
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •