On 14 May, 22:25, Michael Austin <maus...@firstdbasource.com> wrote:
> 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)

You don't actually require all the case statements. Just code the
criteria on the JOIN condition thus:

CREATE VIEW subtable AS
SELECT
aa.sid,
a.fval First,
b.fval Last,
c.fval Team,
d.fval DOB
FROM aaa aa
LEFT JOIN aaa a ON aa.sid = a.sid AND a.fname = 'First'
LEFT JOIN aaa b ON aa.sid = b.sid AND b.fname = 'Last'
LEFT JOIN aaa c ON aa.sid = c.sid AND c.fname = 'Team'
LEFT JOIN aaa d ON aa.sid = d.sid AND d.fname = 'DOB'
;