I have two tables that I would like to get results from without cross-joining to get duplicate data.
Table 1: booksCheckedOut
row | userID | book | date
-------------------------------------------------
1 | 3 | book1 | Jan-26
2 | 3 | book2 | Jan-27
3 | 3 | book3 | Jan-28
4 | 3 | book4 | Jan-29
Table2: numberOfPagesRead
row | userID | name | date | pagesRead
-----------------------------------------------------------------
1 | 3 | John | Jan-26 | 4
2 | 3 | John | Jan-27 | 7
3 | 3 | John | Jan-28 | 8
I'm doing a sql query that looks like this:
select booksCheckedOut.book, numberOfPagesRead.pagesRead
from booksCheckedOut, numberOfPagesRead
where booksCheckedOut.userID='3' and numberOfPagesRead.userID='3'
But I get results that look like this: The rows are being multiplied (4 x 3 = 12 results)
book | pagesRead |
-------------------------
book1 | 4 |
book1 | 7 |
book1 | 8 |
book2 | 4 |
book2 | 7 |
book2 | 8 |
book3 | 4 |
book3 | 7 |
book3 | 8 |
book4 | 4 |
book4 | 7 |
book4 | 8 |
This is what I want:
book | pagesRead |
-------------------------
book1 | 4 |
book2 | 7 |
book3 | 8 |
book4 | |
How do I prevent results from getting multiplied (cross joined)?
I know it makes no sense, butI don't want to associate the number of pages read to a particular book, it's just the way the current database is already set up.
Thanks in advance!
It would appear the UserId and date are the keys joining these tables:
select co.book, nopr.pagesRead
from booksCheckedOut co join
numberOfPagesRead nopr
on co.userId = nopr.UserId and co.date = nopr.date
where co.userID = '3';
You should learn proper join syntax. A simple rule: Never use commas in the from clause.
I added table aliases; the make queries easier to write and to read.