p3ace p3ace - 4 months ago 9
SQL Question

Mysql Obtaining actual value through FK when Selecting all rows

I need to

select * FROM sections
and get the column values for every row to fill a
JTable
. My problem is that my
adviserId
column on section table is an
INT


And because I'm getting the result set of every column on every row, I cannot issue a
WHERE
clause. I thought of subquery but since
Id
is different on every row, no predetermined
Id
can be supplied on
WHERE
clause.

So If I run my stored procedure, I get just an
int
value for
adviserId
instead of the teacher's name.

I have teachers and sections table.

Teacher

id PK INT
lastName
firstName
middleName
isAdviser
status


Sections

id PK
name
adviserId FK-- REFERENCING `id` column ON teacher table


What would be the best approach? I hope you can help.

Thanks.

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
I've created the final stored procedure based on everyone's suggestion. (THANKS AGAIN all.)

CREATE DEFINER=`root`@`localhost` PROCEDURE `getAllSectionsInfo`()
BEGIN
SELECT
s.`name` AS `Section Name`,
s.`session` AS `Session`,
CONCAT(t.lastName,',',t.firstName,' ',t.middleName) AS Adviser,
s.yearLevel AS `Year Level`,
CONCAT(syStart,'-',syEnd) AS SchoolYear

FROM sections s
INNER JOIN
teacher t on s.adviserId = t.id;
END

Answer

Yes I also think the same, that a simple inner join will do your job. Try the below example..

create table JTable as select T.id as Tid,T.lastName,T.firstName,T.middleName,T.isAdviser,T.status,S.id as Sid,S.name,S.adviserId
from Sections as S
inner join Teachers as T on T.id = S.adviserId

You can apply left join here to make sure that you have all records of Section table either related to Teachers data or with null data. So, now the JTable will have all the columns in that you have put on the selection list.

Comments