homepage Welcome to WebmasterWorld Guest from 54.226.43.155
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
Newbie help - UNION operator
harofreak00




msg:4123247
 10:47 pm on Apr 27, 2010 (gmt 0)

Hey guys, working on a simple query here, but I'm not getting the results I expect. Here is the exact question I am to work:

Find the book code and book title for each book whose price is more than $10 or that was published in Boston.


Here is what I came up with:

SELECT B.BookCode, B.Title
FROM Book B
WHERE B.Price > '10'
UNION
SELECT B.BookCode, B.Title
FROM Book B, Publisher P
WHERE B.PublisherCode = P.PublisherCode;


However, this returns all the Books, even though I shouldn't. What did I do wrong? The chapter I am studying is specifically going over the SET operators (UNION, INTERSECT & MINUS), and the ANY and ALL operators. I figured UNION was closest to the example in the book, so thats why I choose that.

 

LifeinAsia




msg:4123248
 10:57 pm on Apr 27, 2010 (gmt 0)

A few issues...
1) "WHERE B.Price > '10'" Assuming the Book.Price field is numeric, you don't use the single quotes (single quotes are used with char/varchar fields). In effect, you're trying to compare a numeric value to a string.
2) "was published in Boston" I don't see anything in your query checking for Boston.
3) "UNION" I don't know why you would need a UNION operator to do this query. Using OR in the WHERE clause should work nicely.

This should point you in the right direction. Since it's your homework, I don't want to give too much away. :)

harofreak00




msg:4123259
 11:19 pm on Apr 27, 2010 (gmt 0)

Here is a revised version.

SELECT B.BookCode, B.Title
FROM Book B
WHERE B.Price > 10
UNION
SELECT B.BookCode, B.Title
FROM Book B, Publisher P
WHERE P.City = 'Boston'
AND B.PublisherCode = P.PublisherCode;


However, this only displays books more than $10, not the ones from Boston. I'm not 100% sure it has to be a UNION. The section before that was JOIN, IN & EXISTS.

LifeinAsia




msg:4123266
 11:22 pm on Apr 27, 2010 (gmt 0)

What do you get with just
SELECT B.BookCode, B.Title
FROM Book B, Publisher P
WHERE P.City = 'Boston'
AND B.PublisherCode = P.PublisherCode


Also, consider if you really need to use UNION at all. How would you write it if you couldn't use UNION?

[edited by: LifeinAsia at 11:25 pm (utc) on Apr 27, 2010]

harofreak00




msg:4123269
 11:24 pm on Apr 27, 2010 (gmt 0)

I guess its kind of a stupid question, because after further looking, ALL of the Boston books (only 2 of them), are both more then $10. I guess I have found the solution! Thanks for the help.

LifeinAsia




msg:4123275
 11:31 pm on Apr 27, 2010 (gmt 0)

Actually, it's not that stupid of a question. If you do the same query with UNION ALL instead of just UNION, you'll find that the result includes duplicates (in other words, all the result of the first part with all of the result from the second part, without duplicates being removed).

And your price for help- try writing the query without using UNION. :)

harofreak00




msg:4123304
 12:26 am on Apr 28, 2010 (gmt 0)

Thanks for the tip.

As far as not using UNION, maybe something like this?

SELECT B.BookCode, B.Title
FROM Book B, Publisher P
WHERE B.Price > 10
AND P.City = 'Boston'
AND B.PublisherCode = P.PublisherCode;


If I wanted to change the original question from OR to AND, would I still use UNION?

LifeinAsia




msg:4123691
 4:04 pm on Apr 28, 2010 (gmt 0)

WHERE B.Price > 10
AND P.City = 'Boston'

Well, this will give you the same results for this particular data set. But remember that you want all the books over $10 OR printed in Boston. Hint, hint. :) This should also answer your question about using still using UNION.

By the way, you CAN use UNION as you did in your original example. My feeling is that it would be less efficient because the table has to be queried twice. However, if the instructor is one of those "The chapter was about using UNION, so you have to use UNION" types... :) In fact, when I was learning SQL, I seem to remember a similar homework problem. The prof made a comment that he wouldn't take any points off for using a UNION, but recommended that we do things more efficiently in the real world.

LifeinAsia




msg:4123696
 4:07 pm on Apr 28, 2010 (gmt 0)

Personally, about the only times I use UNION is when I need to get similar data from 2 separate tables. Something like (find all the books or toys costing more than $10):
SELECT Toys.Item
FROM Toys
WHERE Toys.Price > 10
UNION
SELECT Books.Item
FROM Books
WHERE Books.Price > 10

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved