SitePoint Sponsor

User Tag List

Results 1 to 5 of 5

Hybrid View

  1. #1
    SitePoint Enthusiast
    Join Date
    Jan 2005
    Location
    london
    Posts
    46
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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 |
    +----------+---------+----------+----------+----------------+------------+
    |        
    |       22124401 |     3000 Allocated      2005-04-19 |
    |       
    37 |       45455656 |     2000 Rejected       2005-04-20 |
    |        
    |       |       93 |     1000 Allocated      2005-04-19 |
    |       
    11 |       |   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      |              |            NULL |         |
    |     
    3000 logo       Dr.   | Mark        Bishop      |              |            NULL |         |
    |     
    4000 swarm      Dr.   | Tim         Blackwell   |              |            NULL |         |
    |     
    2000 srilanka   Dr.   | Sebastian   Danicic     |              |            NULL |         

  2. #2
    SitePoint Addict
    Join Date
    Dec 2004
    Location
    San Francisco
    Posts
    263
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  3. #3
    SitePoint Enthusiast
    Join Date
    Jan 2005
    Location
    london
    Posts
    46
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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      |              |            NULL |         |
    |     
    3000 logo       Dr.   | Mark        Bishop      |              |            NULL |         |
    |     
    4000 swarm      Dr.   | Tim         Blackwell   |              |            NULL |         |
    |     
    2000 srilanka   Dr.   | Sebastian   Danicic     |              |            NULL |         

  4. #4
    SitePoint Addict
    Join Date
    Dec 2004
    Location
    San Francisco
    Posts
    263
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  5. #5
    SitePoint Enthusiast
    Join Date
    Jan 2005
    Location
    london
    Posts
    46
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •