Variables in MySQL

[scaiferw]

Everything I can find in variables seems to be a little more complicated than I need.

I'm using MySQL Query browser, and I can't get this to work in either a script tab or a query tab.

I want to draw a list of club officers from our volunteer database for a mailing list, changing the variable to get a different set.

In a script tab, it seems to run without error, but displays no results.

In a resultset tab, same thing.

Can someone tell me where I'm going wrong?

set @officer = "president";
select concat(fname," ",lname," <",email,">;")
from dmember
where id in (
select @officer
from clubs
where @officer
)
and email <> "";

[chazzy]

It probably is running w/ errors, but the errors pane is well hidden on query browser.

but just to clarify, your query is literally saying the following:


select concat(fname," ",lname," <",email,">;")
from dmember
where id in (
select 'president'
from clubs
where 'president'
)
and email <> "";


so let's say it does return results (the inner query). it returns a set of the literal 'president.' without knowing much about your table, i find it unlikely that any 'id' from dmember, unless the column is improperly named, will == 'president'

for performance concerns, you probably want to turn this into an inner join. something like this


set @officer = 'president';
select concat(d.fname,' ',lname,' <',email,'>,')
from dmember d
inner join clubs c
on c.somecolumn = d.id
and c.anothercolumnThatRepresentsRank = 'president'
where d.email is not null;

[scaiferw]

Hi Chazzy;

Thanks for your reply.

The president attribute of the clubs table stores the membership number of the president of that club. (I'm dealing with 66 Rotary Clubs in a District, with a membership table, dmember, that covers the membership of all the clubs.)

The following query returns a good result;

select concat(fname," ",lname," <",email,">;")
from dmember
where id in (
select president
from clubs
where president
)
and email <> "";

I use 'where president' to ensure that rows with no value entered won't return a value - there may be a more elegant way of doing it such as 'where president <> ""' for readability, but the query works as it is.

I use the subquery to feed that into a query that pulls the particulars of those members from the membership table. I find subqueries more intuitive, but it sounds like you're saying that an inner join is less expensive so I'll look into changing that.

Off the bat I can't get the subquery to work, but when I have some time I'll wrestle with it.

Thanks,

Rob

[chazzy]

Ah! Now I see where the dilemma is. Your query is actually a lot more advanced than I realized (and expected to see).

Anyways, here's a blog post from Roland Bouman about how to create prepared statements in mysql 5, and how to create dynamic SQL. The part that will interest you is about 2/3rd's of the way down.

http://rpbouman.blogspot.com/2005/11/mysql-5-prepared-statement-syntax-and.html

[scaiferw]

Thanks, I'll have a look at that.

Cheers,

Rob