Joining Multiple Tables - Oracle

Question

I m studying multiple table joins this week and have some odd results being returned  Here is the scenario     Using the correct tables  create a query using the traditional join operation that will list the customer first and last name  book title  and order date  formatted as MM DD YYYY with an alias of    Order Date     for all the customers who have purchased books published by  PRINTING IS US    With the database that I m querying against  the correct tables for this query are BOOK CUSTOMER  BOOKS  BOOK ORDER  and PUBLISHER  The statement that I have written returns the information that I need  but it is returning almost 5900 records  I don t see how this can be right  The publisher  Printing is Us  only has 14 books listed in the database and there are only 20 customer records  so even if every customer purchased every Printing is Us book  that would only return 280 records total  Yet I can t figure out what I have wrong  My statement is listed below   SELECT bc firstname  bc lastname  b title  TO CHAR bo orderdate   MM DD YYYY    Order Date   p publishername FROM book customer bc  books b  book order bo  publisher p WHERE publishername    PRINTING IS US      Anyone have any thoughts on what I m missing here    Thanks

User · Answer

You are doing a cartesian join  This means that if you wouldn t have even have the single where clause  the number of results you get would be book customer size times books size times book order size times publisher size   In order words  the result set gets blown up because you didn t add meaningful join clauses  Your correct query should look something like this   SELECT bc firstname  bc lastname  b title  TO CHAR bo orderdate   MM DD YYYY    Order Date   p publishername FROM book customer bc  books b  book order bo  publisher p WHERE bc book id   b book id AND bo book id   b book id  etc   AND publishername    PRINTING IS US     Note  usually it is adviced to not use the implicit joins like in this query  but use the INNER JOIN syntax  I am assuming however  that this syntax is used in your study material so I ve left it in

User · Answer

While former answer is absolutely correct  I prefer using the JOIN ON syntax to be sure that I know how do I join and on what fields  It would look something like this   SELECT bc firstname  bc lastname  b title  TO CHAR bo orderdate   MM DD YYYY    Order         Date   p publishername FROM books b JOIN book customer bc ON bc costumer id   b book id LEFT JOIN book order bo ON bo book id   b book id  etc   WHERE b publishername    PRINTING IS US     This syntax seperates completely the WHERE clause from the JOIN clause  making the statement more readable and easier for you to debug

User · Answer

I recommend that you get in the habit  right now  of using ANSI-style joins  meaning you should use the INNER JOIN  LEFT OUTER JOIN  RIGHT OUTER JOIN  FULL OUTER JOIN  and CROSS JOIN elements in your SQL statements rather than using the  old-style  joins where all the tables are named together in the FROM clause and all the join conditions are put in the the WHERE clause  ANSI-style joins are easier to understand and less likely to be miswritten and or misinterpreted than  old-style  joins    I d rewrite your query as   SELECT bc firstname         bc lastname         b title         TO CHAR bo orderdate   MM DD YYYY    Order Date          p publishername FROM BOOK CUSTOMER bc INNER JOIN books b   ON b BOOK ID   bc BOOK ID INNER JOIN  book order bo   ON bo BOOK ID   b BOOK ID INNER JOIN publisher p   ON p PUBLISHER ID   b PUBLISHER ID WHERE p publishername    PRINTING IS US     Share and enjoy

[sql] Joining Multiple Tables - Oracle

Examples related to sql

Examples related to oracle