HElp in select statment plz

hello i have three tables:

tbl_student holding students details and tb_supervisor holding supervisors details and tbl_allocating holding students who will/are allocated.

i just want to select the names of students who are 'Allocated' by supervisor id '3000'.

key words: super_id = supervisors' id, stud_id: students id

PHP Code:

tbl_allocating: +----------+---------+----------+----------+----------------+------------+ | alloc_id | options | stud_id | super_id | allocated_flag | date_today | +----------+---------+----------+----------+----------------+------------+ | 1 | 1 | 22124401 | 3000 | Allocated | 2005-04-19 | | 37 | 2 | 45455656 | 2000 | Rejected | 2005-04-20 | | 6 | 1 | 93 | 1000 | Allocated | 2005-04-19 | | 11 | 1 | 793271 | 2000 | Allocated | 2005-04-19 | tbl_student: +----------+--------------+-----------+------------+------------------+----------------+ | stud_id | password | fname | lname | allocated_superv | date_allocated | +----------+--------------+-----------+------------+------------------+----------------+ | 22124401 | jamaica123 | Royan | Gooden | 3000 | 2005-04-19 | | 93 | ja | Ivy | Echebima | 1000 | 2005-04-19 | | 222 | bigman | Abdi | Razak | NULL | 0000-00-00 | tbl_supervisor: +----------+------------+-------+-------------+-------------+----------------+-----------------+-----------+ | super_id | password | title | super_fname | super_lname | total_students | total_unlocated | privilege | +----------+------------+-------+-------------+-------------+----------------+-----------------+-----------+ | 1000 | javapro | Prof. | Robert | Zimmer | 4 | NULL | 0 | | 3000 | logo | Dr. | Mark | Bishop | 3 | NULL | 0 | | 4000 | swarm | Dr. | Tim | Blackwell | 3 | NULL | 0 | | 2000 | srilanka | Dr. | Sebastian | Danicic | 4 | NULL | 1 |

Try this:

SELECT fname, lname FROM tbl_student as S
INNER JOIN tbl_allocating as A on S.stud_id = A.stud_id
AND A.super_id=3000

Thanx alot it worked. Got now last selection

i need to select all in tbl_student and should display supervisors name on allocate_superv (which is holding supervisors id)

PHP Code:

tbl_student +----------+--------------+-----------+------------+------------------+----------------+ | stud_id | password | fname | lname | allocated_superv | date_allocated | +----------+--------------+-----------+------------+------------------+----------------+ | 22124401 | jamaica123 | Royan | Gooden | 3000 | 2005-04-19 | | 93 | ja | Ivy | Echebima | 1000 | 2005-04-19 | | 222 | bigman | Abdi | Razak | NULL | 0000-00-00 | | 1977 | password | Mohamed | Ashur | NULL | 0000-00-00 | | 3212 | banana | Angelly | Johns | NULL | 0000-00-00 | | 3232 | juice | Gazz | Ben | NULL | 0000-00-00 | tbl_supervisor: +----------+------------+-------+-------------+-------------+----------------+-----------------+-----------+ | super_id | password | title | super_fname | super_lname | total_students | total_unlocated | privilege | +----------+------------+-------+-------------+-------------+----------------+-----------------+-----------+ | 1000 | javapro | Prof. | Robert | Zimmer | 4 | NULL | 0 | | 3000 | logo | Dr. | Mark | Bishop | 3 | NULL | 0 | | 4000 | swarm | Dr. | Tim | Blackwell | 3 | NULL | 0 | | 2000 | srilanka | Dr. | Sebastian | Danicic | 4 | NULL | 1 |

Try this:

SELECT super_id, title, super_fname, super_lname FROM tbl_supervisor as A
INNER JOIN tbl_student as B on A.super_id = B.allocated_superv

RE: Thanx

i tried to do this :
SELECT super_id, CONCAT(fname,' ', lname) as student, CONCAT(super_fname,' ', super_lname) as allocated_superv, date_allocated FROM tb
l_student as st INNER JOIN tbl_supervisor su on su.super_id = st.allocated_superv;

but didnt display whole result, it only displayed those students who r allocated but left out those with NULL as status:
this is the result:

PHP Code:

+----------+-----------------+-------------------+----------------+ | super_id | student | allocated_superv | date_allocated | +----------+-----------------+-------------------+----------------+ | 3000 | Royan Gooden | Mark Bishop | 2005-04-19 | | 1000 | Ivy Echebima | Robert Zimmer | 2005-04-19 | | 11000 | Mohamed Mahzeer | Abdi Singab | 2005-04-20 | | 2000 | Adil Chatur | Sebastian Danicic | 2005-04-20 | | 3000 | Ishaq Majid | Mark Bishop | 2005-04-20 | | 6000 | George Cloony | James Ohene | 2005-04-20 | | 2000 | Salim Hathbul | Sebastian Danicic | 2005-04-19 | +----------+-----------------+-------------------+----------------+

this is wat i want to display but replacing allocated_superv olum with supevisors name

PHP Code:

tbl_student: +----------+--------------+-----------+------------+------------------+----------------+ | stud_id | password | fname | lname | allocated_superv | date_allocated | +----------+--------------+-----------+------------+------------------+----------------+ | 22124401 | jamaica123 | Royan | Gooden | 3000 | 2005-04-19 | | 93 | ja | Ivy | Echebima | 1000 | 2005-04-19 | | 222 | bigman | Abdi | Razak | NULL | 0000-00-00 | | 1977 | password | Mohamed | Ashur | NULL | 0000-00-00 | | 3212 | banana | Angelly | Johns | NULL | 0000-00-00 | | 3232 | juice | Gazz | Ben | NULL | 0000-00-00 | | 7212 | telegraph | Ghita | Ablad | NULL | 0000-00-00 | | 8000 | strawberries | Aalia | Sheikh | NULL | 0000-00-00 | | 2441565 | new | Royan | Gooden | NULL | 0000-00-00 | | 7957275 | silverst | Sheikha | Ahmed | NULL | 0000-00-00 | | 10210210 | documentary | Michael | Moore | NULL | 0000-00-00 | | 12031504 | box | Berry | More | NULL | 0000-00-00 | | 12345678 | hello | Jonathan | Travers | NULL | 0000-00-00 | | 14513334 | flower | Meriem | Ablad | NULL | 0000-00-00 | | 20345201 | hollywood | Denzel | Washington | NULL | 0000-00-00 | | 22089162 | asda | Riad | Adib | NULL | 0000-00-00 | | 22097013 | kenya | Sanjay | Asani | NULL | 0000-00-00 | | 22122629 | standyth | Mohamed | Mahzeer | 11000 | 2005-04-20 | | 22122754 | mombasa | Amor | Esry | NULL | 0000-00-00 | | 22174502 | computer | Nabeel | Dadabhoy | NULL | 0000-00-00 | | 22222221 | lion | Sultan | Yusuf | NULL | 0000-00-00 | | 22223334 | newcross | Hussein | Chatur | NULL | 0000-00-00 | | 23456789 | bath | Adil | Chatur | 2000 | 2005-04-20 | | 32382650 | mombasak | Saleh | Said | NULL | 0000-00-00 | | 32563344 | crisps | Dugagdjin | Qallaku | NULL | 0000-00-00 | | 33265412 | london | Gabby | Amy | NULL | 0000-00-00 | | 40512045 | wicked | Rocco | Siffredi | NULL | 0000-00-00 | | 45455656 | mmmy | Ishaq | Majid | 3000 | 2005-04-20 | | 50000000 | hello | Collin | Agbabiaka | NULL | 0000-00-00 | | 58745147 | beckham | Ria | Mamujee | NULL | 0000-00-00 | | 63021301 | tabloid | Alan | Turing | NULL | 0000-00-00 | | 65403210 | cartoon | Papa | Smurf | NULL | 0000-00-00 | | 65656565 | password | Aisham | Aisham | NULL | 0000-00-00 | | 89457589 | donkey | Darris | Wilson | NULL | 0000-00-00 | | 98989875 | peters | George | Cloony | 6000 | 2005-04-20 | | 99887766 | jahjah | Ivy | Echebima | NULL | 0000-00-00 | | 793271 | virus | Salim | Hathbul | 2000 | 2005-04-19 | +----------+--------------+-----------+------------+------------------+----------------+

thanx