SQL query problem help needed?

Question

I have four different tables customer,author,book,orders...each contain data my query must...

List the book isbn, title, author and quantity required of the book and the
customer name and address for all orders placed.

Can any one give me an outline of how to do this?

Answer 1

1) on paper, draw 4 boxes representing each table



2) List each field in each box (table)



3) Draw a line between tables 1 and 2, 2 and 3, and 3 and 4 so that the lines connect fields that are related (that is, contain values that exist in both tables)



4) Now, highlight the fields from the tables you want.



Here's one way you construct the query:

a) use a select statement "SELECT"



b) list the names of each field (qualified by the name of the table by prefixing the field name with the table's name and a dot) and separate each field with a comma



c) use a FROM clause



d) list the 4 tables separated with commas



e) use a WHERE clause



f) using the lines you drew (there should probably be 1 line between each, so you probably have 3 lines), list the fields (again, qualified by the table name and a dot) so that the field on one side of the line EQUALS the field on the other side of the line, and separate each clause with a comma



g) add additional WHERE clauses depending on your assignment



(this is the older method that's easier to explain than by having you use a JOIN statement)



Your 2nd paragraph is a bit unclear; however, you do have an outline, and should be able to figure out the rest.

Answer 2

What you need to do is use joins in your query. http://www.w3schools.com/sql/sql_join.asp



What you join will be based on your database structure. But from the objects you have mentioned, I'm guessing that the orders table has a customerID FK and a bookID FK (or ISBN number, which is also unique). Then you would get the name of the author through the Author FK in the Book table.



Learn the syntax of the join you need and look at your db structure to figure out what to join on. Start off joining two tables, then add one more, then add the last one.

Answer 3

Assuming you don't want a Cartesian product of all possible row combinations (which is going to be true 99.999999% of the time), you want to use inner joins (either implicitly or explicitly) in the query.



An explicit join uses the JOIN...ON clause while an implicit one specifies an equality in the WHERE clause. For example, assuming that authID is the primary key in table authors and a foreign key in table book, either of these would do:



SELECT FROM book b JOIN author a ON b.authID = a.authID



SELECT FROM book b, author a WHERE b.authID = a.authID

Answer 4

Use 'JOIN' to query more than one table based on the primary / foreign key relationship between the tables.



http://beginner-sql-tutorial.com/sql-joins.htm

Answer 5

i'm no longer as lots of an authority as others that use this website... yet as you have created an alias for p.call (ie Patient_name), ought to you employ Patient_name ASC as your ORDER by way of clause somewhat of p.call ASC? nicely worth a try, besides...

Answer 6

Why do not you give at least the table structure?



"for all orders"



From here you should start.