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?
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
Thank you Guido.
I am coding in PHP and unfortunately the table is structured as posted.
My initial thought was something like this:
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.
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 --
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
WHERE county = 'County D'
Thank you for the information.
The objective is to select a county and only return the column names containing a 'y'.
did you try running the query i posted?
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.
This topic is now closed. New replies are no longer allowed.