Inner Join Four tables

Question

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?

@ SchmitzIT That's just a sample data cant add all data in my DB question edited. — Sahil, Oct 20 '14 at 17:45
@SchmitzIT yeah it works. MANY Thanks. It should be ReturnDate IS NULL NOT ReturnDate = NULL. — Sahil, Oct 20 '14 at 17:53
I'll make an answer out of it. Edit: Done, with some additional info. — SchmitzIT, Oct 20 '14 at 17:57

score 1 · Accepted Answer · answered Oct 20 '14 at 17:56

1

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

answered Oct 20 '14 at 17:56

ChathurawinD

756
1
13
35

score 0 · Answer 2 · edited May 23 '17 at 11:57

0

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

edited May 23 '17 at 11:57

Community

1
1

answered Oct 20 '14 at 17:40

mannyyysh

339
1
6

That's just a sample data cant add all data in my DB question edited. – Sahil Oct 20 '14 at 17:45
1

change your = NULL to IS NULL ex ReturnDate IS NULL – mannyyysh Oct 20 '14 at 17:56

score 0 · Answer 3 · answered Oct 20 '14 at 18:00

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.

Inner Join Four tables

TitleName | ISBN

CopyID | ISBN

BorrowerID | CopyID | BorrowedDate |ReturnDate

BorrowerID | BorrowerName | BorrowerAddress

3 Answers3