joining multiple tables in an SQL query

Question

I'm trying to select information from multiple tables within my database which display the details of multiple books and orders.

I am querying the isbn into 5 tables (author, bookauthor, book, orderline, and bookorder) of a certain book to retrieve information about the book and information for orders that have been placed for that book.

SELECT orderline.isbn, title, ordernumber, orderdate, customername, numcopies, orderline.bookprice, authorname
    FROM author natural join bookauthor  natural join book join orderline natural join bookorder
        WHERE orderline.isbn = book.isbn
        and book.isbn = "1491936169"

which gives me:

ISBN: 1491936169    Title: Kafka: The Definitive Guide: Real-Time Data and Stream Processing at Scale
                    Author: Neha Narkhede, Todd Palino, Gwen Shapira
Order Number  Date       Customer             Copies  Price     Total
N201699998    2016-12-24 Mary Hall                 2   33.99     67.98
N201799999    2017-01-03 Aran Clauson              1   33.99     33.99
Total:

However there are some isbns which have not been ordered and have thus are not present in the orderline table but are in the book table, which displays an isbn for all books.

I want to display the book information for those books which do not have orders as well such as:

ISBN: 0387848576    Title: The Elements of Statistical Learning
                    Author: Jerome Friedman, Trevor Hastie, Robert Tibshirani
No orders

essentially I want a table displaying the book information for the isbn and null values where there are no orders. I imagine this would be some sort of natural outer join, however my attempts have resulted in empty tables.

Updated query attempting to remove duplicates using group by

SELECT ordernumber, orderdate, customername, orderline.isbn, title, orderline.numcopies, stock, shipmentbook.numcopies as shipcopies, authorname
FROM author natural join bookauthor natural join book left join bookorder natural join orderline
                ON book.isbn = orderline.isbn 
                left join mousavs.shipmentbook
                ON book.isbn = shipmentbook.isbn
                WHERE stock > orderline.numcopies
                GROUP BY ordernumber
                ORDER BY  orderdate, ordernumber, ISBN

Error Code: 1055. Expression #4 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'mousavs.orderline.isbn' which is not functionally dependent on columns in GROUP BY clause

`select....from book left join ...`[W3 Schools](https://www.w3schools.com/sql/trysql.asp?filename=trysql_select_join_left) — S3S, Jun 09 '17 at 13:35
@scsimon left joining like so gives me a synax error (unexpected where) for my preceding where clause. — Silverfin, Jun 09 '17 at 13:40
I don't understand what your last comment means--but i would avoid natural join almost exclusively. — S3S, Jun 09 '17 at 13:42
@scsimon Im using left join but I believe it requires the on clause, this still gives me a blank table? — Silverfin, Jun 09 '17 at 13:57
Yes, it requires an on clause. Tell it what you want to join on. — S3S, Jun 09 '17 at 13:58
@scsimon ok i've got it thank you, natural joined author and bookauthor to get those values as well when no order was present. — Silverfin, Jun 09 '17 at 14:05
@Silverfin [NATURAL JOIN](https://stackoverflow.com/questions/6039719/is-natural-join-considered-harmful-in-production-environment) should be avoided in general. You're letting the query engine do the decision making instead of telling it what you want. — Bacon Bits, Jun 09 '17 at 14:13
@BaconBits how could I remove duplicate rows such that an entry here is not being repeated 3 times each here? — Silverfin, Jun 09 '17 at 16:34
@Silverfin That can be done in MySQL, but it's [rather complicated](https://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/) because MySQL doesn't support ROW_NUMBER() like most RDBMSs. — Bacon Bits, Jun 09 '17 at 18:42
@BaconBits I beleve I can do it by group by however I get an error unless I add those different fields to the group by, see my updated query. — Silverfin, Jun 09 '17 at 18:59
@Silverfin Correct. In previous versions, [MySQL the Group By extensions](https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html) allowed that, but ANSI Group By requires deterministic queries. You can use the [`ANY_VALUE()` aggregate function](https://dev.mysql.com/doc/refman/5.7/en/miscellaneous-functions.html#function_any-value) instead if you don't care about determinism, but you should know that that isn't best practice. — Bacon Bits, Jun 12 '17 at 13:06

score 3 · Accepted Answer · answered Jun 09 '17 at 14:02

3

Left join 101... and you really....really need to read up on joins if you plan to work in RDMS.

select
    b.isbn
    ,b.title
    ,b.bookprice
    ,b.stock
    ,a.authorname
    ,o.ordernumber
    ,o.numcopies
    ,o.price
from
    book b
    inner join 
        BookAuthor ba on
        ba.isbn = b.isbn
    inner join
        Author a on
        a.authorid = ba.authorid
    left join
        orderline o on
        o.isbn = b.isbn
    left join
        bookorder bo on
        bo.ordernumber = o.ordernumber
where
    b.isbn = 1491936169

answered Jun 09 '17 at 14:02

S3S

24,809
5
26
45

Do you know of a way I remove duplicate rows such that an entry here is not being repeated 3 times each? – Silverfin Jun 09 '17 at 17:05
Select distinct – S3S Jun 09 '17 at 17:11
distinct will only filter out whole rows that are unique however, and each ow is technically distinct. Unless there is some group by logic that would work. – Silverfin Jun 09 '17 at 17:40
That is exactly what you said in your comment – S3S Jun 09 '17 at 17:40

joining multiple tables in an SQL query

1 Answers1