u812 — 2013-09-03T15:39:39-04:00 — #1
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?
guido2004 — 2013-09-04T02:25:10-04:00 — #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
u812 — 2013-09-04T09:27:23-04:00 — #3
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.
r937 — 2013-09-04T14:28:20-04:00 — #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 --
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'
u812 — 2013-09-04T14:47:28-04:00 — #5
Thank you for the information.
The objective is to select a county and only return the column names containing a 'y'.
r937 — 2013-09-04T17:48:43-04:00 — #6
did you try running the query i posted?
u812 — 2013-09-04T18:12:44-04:00 — #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.
r937 — 2013-09-04T18:51:48-04:00 — #8