Joining tables in query with no common fields

Question

I am creating a database for a library with the following four tables.

Table 1 - book

isbn                title            author
111-2-33-444444-5   Pro JavaFX       Dave Smith
222-3-44-555555-6   Oracle Systems   Kate Roberts
333-4-55-666666-7   Expert jQuery    Mike Smith

Table 2 - copy

code    isbn                   duration
1011    111-2-33-444444-5      21
1012    111-2-33-444444-5      14
1013    111-2-33-444444-5      7
2011    222-3-44-555555-6      21
3011    333-4-55-666666-7      7
3012    333-4-55-666666-7      14

Table 3 - student

no      name      school       embargo
2001    Mike      CMP          No
2002    Andy      CMP          Yes
2003    Sarah     ENG          No
2004    Karen     ENG          Yes
2005    Lucy      BUE          No

Table 4 - loan

code    no      taken       due         return
1011    2002    2015.01.10  2015.01.31  2015.01.31
1011    2002    2015.02.05  2015.02.26  2015.02.23
1011    2003    2015.05.10  2015.05.31  
1013    2003    2014.03.02  2014.03.16  2014.03.10
1013    2002    2014.08.02  2014.08.16  2014.08.16
2011    2004    2013.02.01  2013.02.22  2013.02.20
3011    2002    2015.07.03  2015.07.10  
3011    2005    2014.10.10  2014.10.17  2014.10.20

I have been trying to create a SELECT query that fetches the isbn and title from 'book', the no and name from 'student' but only displays results for books with a duration of 7 days.

I have tried joining the tables using an INNER JOIN and using a LIKE clause for 'code' in the 'copy' table to filter the duration but nothing seems to be working for me. I'm having trouble joining 'book' and 'student' as they have no common columns. Is there a way to get around this?

Edit

Okay so the following query seems to work correctly in the most part, apart from the WHERE LIKE at the end;

SELECT book.isbn, book.title, copy.code, copy.duration, student.no, student.name FROM book INNER JOIN copy ON copy.isbn = book.isbn INNER JOIN loan ON loan.code = copy.code INNER JOIN student ON student.no = loan.no WHERE copy.code LIKE 1013 AND copy.code LIKE 3011

I also tried

WHERE copy.code LIKE 1013 AND 3011

Which only displays results with 1013. I'm probably missing something simple, any suggestions?

Answer 1

There doesn't appear to be a direct relationship between book and student

• There is a relationship between book and copy (isbn)
• There is a relationship between copy and loan (code)
• There is a relationship between loan and student (no)

Therefore we do have an indirect relationship between book and student

Try this on for size:

SELECT book.isbn
     , book.title
     , book.author
     , copy.code
     , copy.duration
     , loan.taken
     , loan.due
     , loan.return
     , student.no
     , student.name
     , student.school
     , student.embargo
FROM   book
 INNER
  JOIN copy
    ON copy.isbn = book.isbn
 INNER
  JOIN loan
    ON loan.code = copy.code
 INNER
  JOIN student
    ON student.no = loan.no

P.S. do you control this data structure? If so there are some changes that could be recommended.