Why is this a SQL syntax error?

select id from school_year where lower(school_year_name) = select
lower(i.enrollment_status) from interns.interns i, interns.student s
where i.unique_key = s.unique_key group by i.id;


produces

ERROR 1064 (42000): You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right
syntax to use near 'select lower(i.enrollment_status) from
interns.interns i, interns.student s wher' at line 1

But if I do just this:

select lower(i.enrollment_status) from interns.interns i,
interns.student s where i.unique_key = s.unique_key group by i.id;

It works like a charm!

I just don't get it! This is why I need a DBA!

Phil

Re: Why is this a SQL syntax error?

select id from school_year where lower(school_year_name) in (select
lower(i.enrollment_status) from interns.interns i, interns.student s
where i.unique_key = s.unique_key group by i.id);

Re: Why is this a SQL syntax error?

kevinjbowman wrote:
> select id from school_year where lower(school_year_name) in (select
> lower(i.enrollment_status) from interns.interns i, interns.student s
> where i.unique_key = s.unique_key group by i.id);

Thanx but I get timeout errors now trying that query, perhaps I have
the whole thing all wrong.

Consider these tables

student

id int not null auto_increment, primary key (id),
school_year_id int not null,
unique_key varchar(16) not null


interns

id int not null auto_increment, primary key (id),
enrollment_status varchar(40) not null,
unique_key varchar(16) not null


school_year

id int not null auto_increment, primary key (id),
school_year_name varchar(40) null


OK what I want to do is extremely easy, but beyond my ability to write
this (I need a DBA!)

Foreach record in student I want to put in the field column
school_year_id the value of school_year.id where student.unique_key =
interns.unique_key **AND** upper(interns.enrollment_status) =
upper(school_year.school_year_name).

I hope that makes sense, but that's as clear as I can do it w/o a
whiteboard to show you exactly what I want.

Sorry, I simply can't do something like this, I've tried for weeks to
no avail.

Thanx
Phil

Re: Why is this a SQL syntax error?

wrote in message
news:1141228398.785786.200600@i39g2000cwa.googlegroups.com.. .
> OK what I want to do is extremely easy, but beyond my ability to write
> this (I need a DBA!)

Philip, this is going to go on until you make this requirement clear to your
managers. If you take weeks to do things that should be done in minutes,
neither you nor the managers are getting a good deal.

> Foreach record in student I want to put in the field column
> school_year_id the value of school_year.id where student.unique_key =
> interns.unique_key **AND** upper(interns.enrollment_status) =
> upper(school_year.school_year_name).

This is the solution to what you're describing:

update students s, interns i, school_year y
set s.school_year_id = y.id
where s.unique_key = i.unique_key
and upper(i.enrollment_status) = upper(y.school_year_name)

However, this is missing something. You don't say how to relate the rows in
student to the rows in either interns or school_year. It's like there's a
join condition missing. Is there some correlation between students and
interns?

Regards,
Bill K.

Re: Why is this a SQL syntax error?

Bill Karwin wrote:
> wrote in message
> news:1141228398.785786.200600@i39g2000cwa.googlegroups.com.. .
> > OK what I want to do is extremely easy, but beyond my ability to write
> > this (I need a DBA!)
>
> Philip, this is going to go on until you make this requirement clear to your
> managers. If you take weeks to do things that should be done in minutes,
> neither you nor the managers are getting a good deal.
>

I made it clear, we were told it wasn't in the budget for the DBA, so
my hands were tied. I am going to make it clear that moving forward a
DBA is required, if that helps.

> > Foreach record in student I want to put in the field column
> > school_year_id the value of school_year.id where student.unique_key =
> > interns.unique_key **AND** upper(interns.enrollment_status) =
> > upper(school_year.school_year_name).
>
> This is the solution to what you're describing:
>
> update students s, interns i, school_year y
> set s.school_year_id = y.id
> where s.unique_key = i.unique_key
> and upper(i.enrollment_status) = upper(y.school_year_name)
>
> However, this is missing something. You don't say how to relate the rows in
> student to the rows in either interns or school_year. It's like there's a
> join condition missing. Is there some correlation between students and
> interns?
>

There is no consistent correlation between students and interns except
the "unique_key" field, which in and of itself sometimes isn't
consistent due to bad data.

Phil

> Regards,
> Bill K.