Post reply

Display distinct record and return result from another table

  • June 23, 2006 at 4:59 am

    #90234

    Hi,

    Let says, i've these following table

    Student

    ID | Name

    -----------------

    001 | Karen

    002 | Robert

    003 | Zana

    BorrowBook

    ID | BookName

    --------------------

    001 | PHP - Web Programming

    001 | MySQL 5.0

    002 | Pro MySQL

    002 | Pro PHP

    002 | Linux

    I've try to query but Zana not retured the result.

    I need to know, How many each student borrowing from BorrowBook. The expected result shown as following

    ID | Name | No Of Book

    ---------------------------

    001 | Karen | 2

    002 | Robert | 3

    003 | Zana | 0

    Please help me ;(.

  • June 23, 2006 at 5:54 am

    #645498

    Sharul - you need a left outer join for this since you want all IDs present in your Student table....

    select a.ID, a.Name, count(b.BookName) as NumOfBooks
from student a
left outer join
BorrowBook b
on a.ID = b.ID
group by a.ID, a.Name
order by a.ID







    **ASCII stupid question, get a stupid ANSI !!!**

  • June 23, 2006 at 11:35 am

    #645637

    tq sushila. . it's work.

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply