Help with a basic query

Databases
1 

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

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:


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

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

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 –

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'
5

Thank you for the information.
The objective is to select a county and only return the column names containing a ‘y’.

6

i know

:slight_smile:

did you try running the query i posted?

7

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

GET OUT!

:slight_smile: