Michael Austin wrote:
> 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 |
> +------+-------+--------+-------+------------+


hit the send button too soon...

Also this method makes it very difficult to do date arithmetic or date
range searches. It can be done, you will first need to force the date
formats you have chosen into something the database can use - you just
need to be aware that it is not currently in a form for doing date
range searches.

You can create a set of views to make extraction of the data a bit
easier - although due to the fact that MySQL is not yet a real database
engine (at least not the 5.0.22-Debian_0ubuntu6.06-log version I have),
you cannot use the previous select statement in a view, you must do
multiple views like:

mysql> create view subtable as
-> 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 left outer join aaa b on a.sid=b.sid
-> left outer join aaa c on a.sid=c.sid
-> left outer join aaa d on a.sid=d.sid
-> ;
Query OK, 0 rows affected (0.01 sec)

mysql> create view masterview as
-> select sid, first, last, team, dob from subtable
-> where First is not null
-> and Last is not null
-> and Team is not null
-> and DOB is not null;
Query OK, 0 rows affected (0.02 sec)

mysql> select * from masterview;
+------+-------+--------+-------+------------+
| sid | first | last | team | dob |
+------+-------+--------+-------+------------+
| 1 | John | Jones | Boys | 01/01/1990 |
| 2 | Molly | Malloy | Girls | 02/02/1990 |
+------+-------+--------+-------+------------+
2 rows in set (0.00 sec)