> I have 3 tables with similar structures: students, staff,
> and faculty. I
> want to select lname,fname,username from all three in ONE SQL
AFAIK there is no way to do this with one simple SQL statement. There are a
couple of different options I can think of immediately.
Create a temporary table which contains all of the records, and do your
select on that. This isn't that easy with mysql as you'd have to do three
SELECT INTO's and then three LOAD DATA's - very inefficient. Not nice.
A better way, and the one that I'd recommend is to restructure you database,
so instead of having three tables you have only one. Instead of having
tables 'staff', 'students' etc, have a SET field or something similar so you
can categorise the entries in the table into staff, students or whatever.
This is extensible, so if you get another type of user then it won't be hard
to add them to your other code.
It's also got to be the most efficient. There's no JOINs, no throwing data
in or out, no nothing. Simple is best.
If the data is largely the same then this should be relatively easy to do.