| Author |
Topic |
|
disciple
Starting Member
27 Posts |
 Posted - 2006-04-16 : 15:19:45
|
| Ok, im still learning sql and ive just hit a wall with joins. I'm confused out of my mind, even after reading about it on multiple websites and my textbook.yes this is for school *gasp* but im not asking for answers, I really am trying to learn this stuff. I've managed to get the very basics of joins down,but as soon as it starts getting even the slightest bit complicated i have no idea what to do.Here are two of the problems I just dont know how to proceed on. I'm hoping that maybe someone can walk me through one of them so i can really see what is going on, and possibly answer any other questions I have.Write a query using the BOOKS and PUBLISHER tables that will show all of the publisher’s names and related book titles, even those publishers who do not currently have any books listed in the BOOK table. HINT: this will require an OUTER JOINSELECT NAME,TITLE FROM BOOKS,PUBLISHER WHERE BOOKS.PUBID = PUBLISHER.PUBID(+);I dont know if this is right but this is what I came up with. It gives me the same results even if I take out the "(+)" so im fairly certain im not doing it correctly.Here is another question:Using the BOOK_CUSTOMER, BOOK_ORDER and ORDERITEMS tables, create a query using the JOIN method to display a listing of all customers who have purchased a QUANTITY of more than 2 books. List the customer’s last name, the customer’s city and state, and the total quantity of books they have purchased. Give the total quantity purchased column and alias of “Number Purchased”.SELECT LASTNAME,CITY,STATE,QUANTITY "Number Purchased" FROM BOOK_CUSTOMER, BOOK_ORDER, ORDERITEMS WHERE QUANTITY > 2; I know its asking a lot, but I would really appreciate it if someone could help me understand since it doesnt seem to be clicking. |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-04-16 : 15:36:24
|
| This is oracle not sql severbut try (read about outer joins again - especially about which table the (+) aftects)SELECT NAME,TITLE FROM BOOKS,PUBLISHER WHERE PUBLISHER.PUBID = BOOKS.PUBID(+);For the second question have a look at the group by and having clauses.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
disciple
Starting Member
27 Posts |
Posted - 2006-04-16 : 15:44:15
|
| I appreciate your help. Ok, so it is supposed to go after the column that is missing the corressponding row. I dont understand why it goes with books... ooooh! I get it now. Thankyou for clearing that up. I will go look up group by and having clauses now |
|
|
|
disciple
Starting Member
27 Posts |
Posted - 2006-04-16 : 16:20:30
|
| Well I read about them but im not sure how I would use it in this situation, or why it would be needed. SELECT LASTNAME,CITY,STATE,QUANTITY "Number Purchased" FROM BOOK_CUSTOMER, BOOK_ORDER, ORDERITEMS WHERE QUANTITY>2;This lists 840 rows, but I dont understand whats wrong? I scroll through the data and it seems that it keeps repeating... What am I missing? |
|
|
|
disciple
Starting Member
27 Posts |
Posted - 2006-04-16 : 16:29:47
|
| I apologize for the triple post, I just dont know how to edit a post. Am I supposed tobe using a Natural join witha group by and having? |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-04-16 : 16:50:14
|
| You haven't joined the BOOK_CUSTOMER, BOOK_ORDER, ORDERITEMS tables so you will get a cartsian product.Do that first then we can look at getting the required result.A good way of testing the query will to include a single ORDERITEMS and check that you just get back one row. Do that by adding "and ORDERITEMS.ORDERITEMS_id = ...." (or whatever the PK on hat table is)==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
disciple
Starting Member
27 Posts |
Posted - 2006-04-16 : 17:02:58
|
| ok, im confused about how to join the different tables. Would I do something like this?SELECT LASTNAME,CITY,STATE,QUANTITY "Number Purchased" FROM BOOK_CUSTOMER JOIN BOOK_ORDER JOIN ORDERITEMS WHERE QUANTITY>2the book only really shows examples for when there is one table that needs to be joined. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-04-16 : 17:13:08
|
| You need to find the columns on which the tables are connected.Probably BOOK_CUSTOMER will join to BOOK_ORDER on a customer id.BOOK_ORDER will jion to ORDERITEMS on a order id.something likefrom BOOK_CUSTOMER bcjoin BOOK_ORDER boon bc.CUSTOMER_id = bo.CUSTOMER_idjoin ORDERITEMS oion bo.ORDER_id = oi.ORDER_id==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
disciple
Starting Member
27 Posts |
Posted - 2006-04-16 : 17:32:27
|
| ok, I think i figured it out. here is what I have:SELECT LASTNAME,CITY,STATE,QUANTITY "Number Purchased" FROM BOOK_CUSTOMER bc JOIN BOOK_ORDER bo ON bc.CUSTOMER# = bo.CUSTOMER#JOIN ORDERITEMS oi ON bo.ORDER# = oi.ORDER# WHERE QUANTITY>2;So basically its just making a chain between the tables. Finding the link that will allow the data to be found? |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-04-16 : 17:40:58
|
| Your next problem is that you only have the quantity of books ordered in a single order here.The question asks forcustomers who have purchased a QUANTITY of more than 2 booksand the total quantity of books they have purchasedIt's not clear whether the quantity test refers to a single order or to the total of orserd for the customer but the result field clearly looks for the total.For this you will need a group by clause to get the total for the customer. The total will need to sum the quantity.If the > 2 is for a single order then it will be in the where clause as you have it. If it's the total for the customer then it will be in a having clause.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
disciple
Starting Member
27 Posts |
Posted - 2006-04-16 : 18:15:31
|
| Alright, back to the book! :). Will see if I can figure this out yet |
|
|
|
disciple
Starting Member
27 Posts |
Posted - 2006-04-16 : 19:01:41
|
| Im confused again :(.SELECT LASTNAME,CITY,STATE,QUANTITY "Number Purchased" FROM BOOK_CUSTOMER bc JOIN BOOK_ORDER bo ON bc.CUSTOMER# = bo.CUSTOMER#JOIN ORDERITEMS oi ON bo.ORDER# = oi.ORDER# GROUP BY LASTNAME HAVING QUANTITY>2(SELECT SUM(QUANTITY) FROM BOOK_ORDER);Ok, so im guessing I cant still have a where because now I am using HAVING and GROUP BY, and im doing that because wheres arent able to be used with a group by. Am I going to need to write a subquery to keep track of the total quantity? I just quickly added in that last part, as its the only way I can think of that would allow such a thing. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-04-16 : 19:27:55
|
| You can have any columns in the select that appear in the group by clause. Anything that doesn't appear in the group by has to be an aggregate.Think about it - the group by forms groups if you group by LASTNAME then which quantity will be included for a group (which has a single row for each LASTNAME).In your query you need sum(QUANTITY) in the select.You have a choice between grouping by LASTNAME,CITY,STATE or grouping by CUSTOMER# and putting in the select max(LASTNAME),max(CITY),max(STATE). You should have a full name returned rather than just the last name - even that probably isn't unique.As for the having clause - everything in a having clause should be an aggregate for the group sohaving sum(QUANTITY) > 2or if you are looking for any single order item with more than 2 books (unlikely)having max(QUANTITY) > 2==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
Join Problems