Help with SQL statement

[messpac]

Im having help running a query correctly in ssh. Im new to SQL so im only running basic queries.

I need to list each branc name, book type, and total number of books in stock by each branch and book type.

So im confused as to how to do a join, and to what the statement would look like. Since there are no relation between the two tables, should I create a join in the WHERE clause with a different table that doesn't relate to the statement?

Here is the table output with the Tables that relate to this question

BRANCH (Branch_number, Branch_name, branch_location, number_employees)
BOOK (Book_code, book_title, publisher_code, book_type, book_price, paperback)
INVENT (book_code, branch_number, units_on_hand)

How do I join two tables that have no relation? Should I use the invent table's foriegn keys?

MY statement so far is
SELECT COUNT(b. book_code) AS Total, br. branch_name, b. book_type
FROM book b, branch br
WHERE ?
ORDER BY ?

 

[TheSQL]

Total number of books should be the units_on_hand field, if I don't mistake your tables. Your query should read:

SELECT inv.units_on_hand as Total, br.Branch_Name, b.Book_Type
FROM INVENT inv
JOIN BOOK b ON inv.book_code = b.book_code
JOIN BRANCH br ON inv.branch_number = br.Branch_number

You're essentially joining both tables back to INVENT. INVENT should contain one row for each book/branch combination, and link to one book and branch respectively.

 

[messpac]

Im not familiar with the JOIN command yet. Like I said im in the basics of SQL right now and I only know how to join tables by using the WHERE function. Is there any way to complete the statement using this method (Joining tables with WHERE)?

I want to list each branch name, book type, and the total number of books in stock by each branch and book type

This is what I have so far,

SELECT COUNT (i. units_on_hand) as Total, br. branch_name, b. book_type
FROM invent i, branch br, book b
WHERE i. branch_number = br. branch_number AND i. book_code = b. book_code
GROUP BY branch_name, book_type ?

Thank you for your help with this complicated issue.

 

[JarlH]

New style join (SQL-92 and later):
SELECT inv.units_on_hand as Total, br.Branch_Name, b.Book_Type
FROM INVENT inv
JOIN BOOK b ON inv.book_code = b.book_code
JOIN BRANCH br ON inv.branch_number = br.Branch_number

written as old style (SQL-89 and later):
SELECT inv.units_on_hand as Total, br.Branch_Name, b.Book_Type
FROM INVENT inv, BOOK b, BRANCH br
WHERE inv.book_code = b.book_code
AND inv.branch_number = br.Branch_number

Note that the DBMS may or may not execute the joins in the order as specified in the new style join. (Depends on product's optimizer.)

 

[TheSQL]

If you're learning the basics of SQL and it's teaching you old join syntax, you need to invest in a newer book. There's not much point to learning the old method just to have to learn the new! It's good to know the old syntax for backward compatability, but when you're writing something from scratch, it may as well be up to date. I'd recommend Beginning SQL Server 2005 Programming (

http://www.amazon.com/Beginning-Server-2005-Programming-Programmer/dp/0764584332)

if you're using MSSQL, else SQL for Dummies as a general reference. Have fun!