SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Wizard creole's Avatar
    Join Date
    Oct 2000
    Location
    Nashvegas Baby!
    Posts
    7,845
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    The CASE statement.

    I've read about this elusive beast but I've never been able to get it to work. There's hardly ANY information about it on the mySQL website so I wanted to have a thread here where someone knowledgable about database dev could give me a definitive answer on how and when to use it. Do you use it in the SELECT portion of your SQL? The WHERE? The ORDER BY? I can't beleive that something that could be so uesful is almost completely neglected on the mySQL website.

    I'll start off by posting a query that I'm trying to get working with CASE.

    PHP Code:
    SELECT p.prod_dateaddedCONCAT(m.mfr_name' 'p.prod_name) AS productc.company_nameu.user_name
    FROM tblmanufacturers m
    INNER JOIN tblproducts p
        ON m
    .mfr_id p.fk_mfr_id
    LEFT OUTER JOIN tbluser u
        ON u
    .user_id p.fk_user_id
    LEFT OUTER JOIN tblcompany c
        ON c
    .company_id u.fk_company_id
    WHERE prod_dateadded BETWEEN 
    '2004-11-01' AND '#DateFormat(Now(),"yyyy-mm-dd")#'
    ORDER BY product 
    At this point my query returns 3 rows. Two of those rows have 2 records each that return empty strings. I'd like to replace those empty strings with the letters "EMC". Obviously I could use CFIF statements to replace them but I really want to learn more SQL so I'm asking this question now.

    Does anyone have any thoughts?
    Adobe Certified Coldfusion MX 7 Developer
    Adobe Certified Advanced Coldfusion MX Developer
    My Blog (new) | My Family | My Freelance | My Recipes

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Do you use it in the SELECT portion of your SQL?
    yes

    The WHERE?
    yes

    The ORDER BY?
    yes

    Two of those rows have 2 records each
    ????

    I'd like to replace those empty strings with the letters "EMC".
    CASE WHEN foo='' THEN 'EMC' ELSE foo END
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard creole's Avatar
    Join Date
    Oct 2000
    Location
    Nashvegas Baby!
    Posts
    7,845
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry...each row returns 4 colums, two of which might be empty strings.

    Rudy...you gave me an example of the CASE statement, but where do I put it? Do I SELECT it first or do I say WHERE CASE...?
    Adobe Certified Coldfusion MX 7 Developer
    Adobe Certified Advanced Coldfusion MX Developer
    My Blog (new) | My Family | My Freelance | My Recipes

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    that depends on what you want, which i don't think i understand yet
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Wizard creole's Avatar
    Join Date
    Oct 2000
    Location
    Nashvegas Baby!
    Posts
    7,845
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I see....

    Using that query above, I get these results:
    Date|Added|Product|Company|User Name
    12-16-2004|10 Strawberry Street|ABC123|Hertz Rentals (test) Tim Quarrel
    12-16-2004|10 Strawberry Street|Model One|Matthews Unit Management|George Bush
    12-16-2004|Atlas Copco|XAS79||

    You can see that in the last row, two of the results (company name and user name) are empty strings. So what I want to know how to do is to return the letters EMC in place of those empty strings in however many instances there are.
    Adobe Certified Coldfusion MX 7 Developer
    Adobe Certified Advanced Coldfusion MX Developer
    My Blog (new) | My Family | My Freelance | My Recipes

  6. #6
    SitePoint Wizard creole's Avatar
    Join Date
    Oct 2000
    Location
    Nashvegas Baby!
    Posts
    7,845
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Okay...

    So I'm using this query now:
    PHP Code:
    SELECT p.prod_dateaddedCONCAT(m.mfr_name' 'p.prod_name) AS productu.user_name,
        CASE 
    WHEN c.company_name '' THEN 'EMC' ELSE c.company_name END AS company_name
    FROM tblmanufacturers m
    INNER JOIN tblproducts p
        ON m
    .mfr_id p.fk_mfr_id
    LEFT OUTER JOIN tbluser u
        ON u
    .user_id p.fk_user_id
    LEFT OUTER JOIN tblcompany c
        ON c
    .company_id u.fk_company_id
    WHERE prod_dateadded BETWEEN 
    '2004-11-01' AND '#DateFormat(Now(),"yyyy-mm-dd")#'
    ORDER BY product 
    It doesn't really appear to change anything. The way I read this query is if company_name is empty (or ''), then use the text 'EMC', if not, then use company_name. It's not doing this though. Am I doing something wrong or leaving something out?
    Adobe Certified Coldfusion MX 7 Developer
    Adobe Certified Advanced Coldfusion MX Developer
    My Blog (new) | My Family | My Freelance | My Recipes

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    empty is not the same as null

    in a LEFT OUTER JOIN, you will get nulls in columns from right table(s) where the row doesn't have a match

    instead of your CASE expression, try this --

    select coalesce(company_name,'EMC') as company_name ...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Wizard creole's Avatar
    Join Date
    Oct 2000
    Location
    Nashvegas Baby!
    Posts
    7,845
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Rudy! That worked perfectly! Thank you!!

    Just for future reference, is is possible to do the same thing with CASE? Another friend suggested I try the ISNULL function mixed in the CASE statement. Would that work too?
    Adobe Certified Coldfusion MX 7 Developer
    Adobe Certified Advanced Coldfusion MX Developer
    My Blog (new) | My Family | My Freelance | My Recipes

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    yes, you can do the same with CASE, it's just wordier

    i stay away from functions like ISNULL because i always have to look them up in the particular database system (mysql, sql server, etc.) to confirm that they does what i want them to do, and to make sure i'm not mixing up the syntax for ISNULL with NULLIF, etc.

    COALESCE is standard and works the same in all databases
    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
  •