HElp in select statment plz

[amoresry]

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

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 | 

[dharamgollapudi]

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

[amoresry]

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)

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 | 

[dharamgollapudi]

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

[amoresry]

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:

+----------+-----------------+-------------------+----------------+
| 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

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