SQL giving me lines that doesn't exist?

Question

While using this:

SELECT borrowbook.studentusername, borrowbook.schoolbookid,borrowbook.date,borrowbook.deadline, book.title, student.email, student.fname, student.lname
FROM borrowbook, book, student

I get many lines, but in my database I just have four lines in the borrowbook table, and while using this, I get some "lines" that doesn't exist. (Note: this works through php on a website, I cannot seem to make this work in mysql so I think I have done something)

Like that a person that had borrowed one book (line 1 in my list of borrowed books) suddenly has borrowed ten different books that I have not registered anyone to borrow. With date as to when it was loaned, and deadline just taken from one of the four lines I have registered.

Even the same person that is registered to borrow one book, suddenly shows up as if they borrowed it four times with different dates. Dates and deadline are taken from "borrowbook" while different names of students are taken from another table, since they have never been used in the "borrowbook" line.

I have tried this now in different ways and with different content and different tables, but still get many "made up" lines of loans that is not registered.

I know very little, but I am grateful for all help I can get. Articles help as well.

You have created multiple implicit joins. You should explicitly define your joins instead of letting MySQL guess how they work. — ceejayoz, Apr 23 '21 at 20:15
And implicit joins must be specified in WHERE clause like WHERE borrowbook.schoolbookid=book.schoolbookid ...... — jacouh, Apr 23 '21 at 20:22
Does this answer your question? [How to join two tables mysql?](https://stackoverflow.com/questions/3536283/how-to-join-two-tables-mysql) — devlin carnate, Apr 23 '21 at 20:27
@ceejayoz just to clarify, MySQL won't "guess" how they work. If no join condition is specified, it will always cross join the tables. — Marko, Apr 23 '21 at 21:06
I tried with JOIN and WHERE with what you suggested and got the same, but I do not know the functionality of "inner join" and "on". — soi, Apr 23 '21 at 21:30

jacouh · Accepted Answer · 2021-04-25T09:30:37.743

1

Without joins, you duplicate records. For a better practice, you should use explicit joins instead of implicit ones. If you have student.username and book.id fields, you can do something like this:

SELECT borrowbook.studentusername, borrowbook.schoolbookid,borrowbook.date,borrowbook.deadline,
  book.title,
  student.email, student.fname, student.lname
FROM borrowbook
  INNER JOIN student ON borrowbook.studentusername=student.username
  INNER JOIN schoolbook ON borrowbook.schoolbookid=schoolbook.id
  INNER JOIN book ON schoolbook.isbn=book.isbn
  ;

edited Apr 25 '21 at 09:30

answered Apr 23 '21 at 21:16

jacouh

8,473
5
32
43

But where is the connection between the school book, and the book then? I have a table with information about the book, and then one with all the version of that book which the school has. Sould I just add a INNER JOIN book.isbn=schoolbook.isbn? – soi Apr 24 '21 at 12:40
if schoolbook table is implied in your selection, you shoul add it. – jacouh Apr 24 '21 at 20:11
@soi, iI've added join for schoolbook table. – jacouh Apr 25 '21 at 09:32

Marko · Answer 2 · 2021-04-23T21:08:18.440

0

You haven't specified any JOIN conditions in your query, and because of that tables will be CROSS JOIN-ed, i.e., every record from the borrowbook table is paired with every record from the book table which is then paired with every record from the student table. So if you have X, Y and Z number of records in each table respectively, you will get X * Y * Z records as a result.

You probably want to add join conditions such as (I'm just guessing column names):

SELECT borrowbook.studentusername, borrowbook.schoolbookid,borrowbook.date,borrowbook.deadline, book.title, student.email, student.fname, student.lname
FROM borrowbook, book, student
WHERE borrowbook.book_id = book.id and borrowbook.student_id = student.id

edited Apr 23 '21 at 21:08

answered Apr 23 '21 at 21:02

Marko

803
9
13

But i struggle to add all the different tables, I have four tables (or five really but I don't need last one now) A B C D C is connected by D and B, and A is connected to B. I need a "line" or the connection between A to B, and then B to C so I get the right information from A B C. Did that make sense? Like this: "Author-book-schoolbook-loan-student" I need the information about the author, book and schoolbook, and about the student in connection to the loan of the book if that makes sense? – soi Apr 24 '21 at 12:53
@soi Then just join the tables using appropriate columns, e.g. `A join B on A.x = B.y join C on B.z = C.w join D on ...` – Marko Apr 24 '21 at 15:39

SQL giving me lines that doesn't exist?

2 Answers2