Schemer wrote:
>> Unfortunately, I have no control over the table structure.
>> When this query is used, the error quoted in the original message is
>> thrown.
>> What might a query with explicit JOINs look like?
>
> More detail on table structure and content (subset of data):
> f_id sub_id field_name field_val
> 1 1 First John
> 2 1 Last Jones
> 3 1 Team Boys
> 4 1 DOB 01/01/1990
> 5 2 First Molly
> 6 2 Last Malloy
> 7 2 Team Girls
> 8 2 DOB 02/02/1990
>
> For each sub_id, select all field_vals, if the "Team" field_name for that
> sub_id has the field_val "Boys".
> Regarding the JOINs, I thought they were for querying more than one table.
>
>
>
>

Ahhh.. someone created a sideways database where you have a column in a
row that describes the next column in the row. Whoever created this
mess should be shot. There are very few reasons to create a database in
this manner.

What you need is something along the lines of
mysql> select sid, first, last, team, dob from
-> (select a.sid,
-> case a.fname when 'First' then a.fval end as First,
-> case b.fname when 'Last' then b.fval end as Last,
-> case c.fname when 'Team' then c.fval end as Team,
-> case d.fname when 'DOB ' then d.fval end as DOB
-> from aaa a right outer join aaa b on a.sid=b.sid
-> right outer join aaa c on a.sid=c.sid
-> right outer join aaa d on a.sid=d.sid) e
-> where sid is not null
-> and First is not null
-> and Last is not null
-> and Team is not null
-> and DOB is not null;
+------+-------+--------+-------+------------+
| sid | first | last | team | dob |
+------+-------+--------+-------+------------+
| 1 | John | Jones | Boys | 01/01/1990 |
| 2 | Molly | Malloy | Girls | 02/02/1990 |
+------+-------+--------+-------+------------+