SQL fetch values from two tables

Question

Hi having a problem on how to structure my sql statment would appreciate any help on how should i do it right. What i am trying to achieve is to show all advisers from tbl_adviser and while also showing the class section_id the teacher is assigned from tbl_section. This is what i have come up so far.

 $qry_display = "SELECT a.section_id,b.*
     from tbl_section As a LEFT OUTER JOIN tbl_adviser AS b ON a.section_id
                                 = b.adviser_id Where bname='$branch'";

My idea on $branch is that the user may only view the same data if they have the same bname

This is my DB structure:

tbl_section:

section_id
section_name
sy
adviser_id
level
bname

tbl_adviser:

adviser_id
lname_a
fname_a
address
bname
photo
cnumber

And this is how i suppose to show:

 while (@$get_display = mysql_fetch_object(@$sql_display))
            {

            ?><tbody>
            <tr>
<td  class="text"><a href="adviser_view.php?&id=
<?php echo $get_display->adviser_id ?>">
            <?php echo @$get_display->adviser_id; ?></a></td>
                <td  class="text"><?php echo @$get_display->lname_a ?></td>
                <td  class="text"><?php echo @$get_display->fname_a ?></td>
                <td  class="text"><?php echo @$get_display->section_id ?></td>


            </tr>
            </tbody>

Edit: Error spatting out is "Column 'bname' in on clause is ambiguous".

Oded · Accepted Answer · 2012-09-20 10:22:26Z

1

Your ON looks to me to be incorrect:

ON a.section_id = b.adviser_id

Should probably be:

ON a.adviser_id = b.adviser_id

You also need to disabmiguate the bname column - SQL doesn't know which table it is supposed to be coming from in the WHERE clause:

Where bname='$branch'

Can be:

Where a.bname='$branch'

Or:

Where b.bname='$branch'

edited Sep 20, 2012 at 10:22

answered Sep 20, 2012 at 9:53

Oded

501k102 gold badges900 silver badges1k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Oded Over a year ago

@Tony - Answer updated. You need to say which bname you mean (from which table).

Collectives™ on Stack Overflow

SQL fetch values from two tables

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related