SitePoint Sponsor

User Tag List

Results 1 to 8 of 8

Hybrid View

  1. #1
    SitePoint Member
    Join Date
    Aug 2013
    Posts
    11
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Help with a basic query

    Code:
    MySql Table:
    
    ctyID       county            s7        s8       s9        s10
    1           County A          n         n        n         n
    2           County B          y         n        n         n
    3           County C          n         y        n         n
    4           County D          y         n        y         n
    I would like select a county and have the column name returned if the field contains a 'y'.

    For example: select "County D" and the query would return "s7" and "s9".

    How do I create a SQL query that will accomplish this task?
    Thank you!

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,401
    Mentioned
    147 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by u812 View Post
    How do I create a SQL query that will accomplish this task?
    Thank you!
    You don't. The way your table is structured, you'll have to do that in your programming language (PHP?).

    If your table structure was like this:

    Code:
    ctyID       county            stype  svalue
    1           County A          s7       n 
    1           County A          s8       n 
    1           County A          s9       n 
    1           County A          s10     n 
    2           County B          s7       y
    2           County B          s8       n
    2           County B          s9       n
    2           County B          s10     n
    then the query you want would've been easy

  3. #3
    SitePoint Member
    Join Date
    Aug 2013
    Posts
    11
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you Guido.
    I am coding in PHP and unfortunately the table is structured as posted.

    My initial thought was something like this:
    SELECT *
    WHERE County = "County D" AND field = 'Y'

    I thought there may be a MySQL option to reference fields within a row, but can't seem to find one?
    I suspect my other option is to return the contents of the row in an array and check each value? Are there other options I may be missing?
    Thank you for your assistance.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by u812 View Post
    I thought there may be a MySQL option to reference fields within a row, but can't seem to find one?
    there is... you just reference the column by name

    however, if you want to return the name of a column rather than the value of a column, then you have to hardcode it --
    Code:
    SELECT CONCAT(
                 CASE WHEN s7 = 'y' THEN 's7' ELSE '' END
               , CASE WHEN s8 = 'y' THEN 's8' ELSE '' END
               , CASE WHEN s9 = 'y' THEN 's9' ELSE '' END
               , CASE WHEN s10 = 'y' THEN 's10' ELSE '' END
                 ) AS columns
      FROM daTable
     WHERE county = 'County D'
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Member
    Join Date
    Aug 2013
    Posts
    11
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you for the information.
    The objective is to select a county and only return the column names containing a 'y'.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by u812 View Post
    The objective is to select a county and only return the column names containing a 'y'.
    i know



    did you try running the query i posted?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Member
    Join Date
    Aug 2013
    Posts
    11
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have not ran the query yet. The real database is fairly large with 500 s-numbers. I was working on another method.
    In PHP using mysqli_fetch_row($County); // to select the entire row
    array_shift(); // to shift out unnecessary data
    array_combine( $reference S numbers, $data ('y' or 'n') ) // Results in an array with: s7 => 'y', s8=>'n', s9=>'y', s10=>'n'
    Now, I just need a way to loop through the array and look for keys that match 'y'. Haven't reached this point yet.

    Thank you for any advice or suggestions.

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by u812 View Post
    The real database is fairly large with 500 s-numbers.
    GET OUT!

    r937.com | rudy.ca | 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
  •