Reputation: 137

Inner Join Four tables

I have Four tables like this.

      Title Table

TitleName | ISBN

      Engineering BK    |1234556
      Time for a change |1233333

Book ISBN is not normal ISBN it's of int type.

 Copy Table

CopyID | ISBN

    1      | 1234556
    2      | 1233333

 Loan Table

BorrowerID | CopyID | BorrowedDate |ReturnDate

    1        |  1     |  2014-10-20  | NULL
    2        |  2     |  2014-10-18  | NULL
    3        |  3     |  2014-10-11  | 2014-10-20

Status of the books which are not returned yet are SAVED as NULL

Borrower Table

BorrowerID | BorrowerName | BorrowerAddress

   1         | Sam            | Shawl Rd, London
   2         | Ram            | ABC Rd, London

I'm going to Select a Title and all the borrowed copies of that title and Borrower details of each copy.

                                                                                          SELECT T.ISBN,T.TitleName,CP.Copy_ID,LN.BorrowerID, BR.BorrowerName,BR.BorrowerAddress FROM  Title T 
                  INNER JOIN Copy CP ON T.ISBN=CP.ISBN
                  INNER JOIN Loan LN ON CP.CopyID=LN.CopyID
                  INNER JOIN Borrower BR 
                  ON LN.BorrowerID=BR.BorrowerID WHERE LN.ReturnDate=NULL 
                  AND T. TitleName='Time For a change';

But this doesn't result anything. What am I doing wrong here?

Upvotes: 1

Answers (3)

SchmitzIT

Reputation: 9552

Change this:

          ON LN.BorrowerID=BR.BorrowerID WHERE LN.ReturnDate=NULL

to this:

          ON LN.BorrowerID=BR.BorrowerID WHERE LN.ReturnDate is NULL

NULL is a special placeholder, usually indicating an unknown value. As such, the normal comparison of something using the = won't apply when you're trying to handle NULL values. Instead you can sidestep it by using IS NULL.

Keep in mind that a data field that is NULLABLE does have some extra bytes of storage. Depending on whether or not you care about that, you could also chose to use a DEFAULT value instead of NULL.

Upvotes: 0

ChathurawinD

Reputation: 784

Change this to

INNER JOIN Borrower BR ON LN.BorrowerID=BR.BorrowerID WHERE LN.ReturnDate=NULL

INNER JOIN Borrower BR ON LN.BorrowerID=BR.BorrowerID WHERE LN.ReturnDate IS NULL

Upvotes: 1

mannyyysh

Reputation: 339

because it is a inner join and to make it work you have to have a values in both tabled to get the row, that title you're trying to select doesn't have value in Copy table. read up on inner join first and how sql joins works

read this link first the you'll know sql joins