Welcome to WebmasterWorld Guest from 54.224.121.67

Forum Moderators: open

Message Too Old, No Replies

Newbie help - UNION operator

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

New User

5+ Year Member

joined:Mar 29, 2010
posts: 8
votes: 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.
10:57 pm on Apr 27, 2010 (gmt 0)

Moderator from US 

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Dec 10, 2005
posts:5710
votes: 88


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. :)
11:19 pm on Apr 27, 2010 (gmt 0)

New User

5+ Year Member

joined:Mar 29, 2010
posts: 8
votes: 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.
11:22 pm on Apr 27, 2010 (gmt 0)

Moderator from US 

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Dec 10, 2005
posts:5710
votes: 88


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]

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

New User

5+ Year Member

joined:Mar 29, 2010
posts:8
votes: 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.
11:31 pm on Apr 27, 2010 (gmt 0)

Moderator from US 

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Dec 10, 2005
posts:5710
votes: 88


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. :)
12:26 am on Apr 28, 2010 (gmt 0)

New User

5+ Year Member

joined:Mar 29, 2010
posts: 8
votes: 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?
4:04 pm on Apr 28, 2010 (gmt 0)

Moderator from US 

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Dec 10, 2005
posts:5710
votes: 88


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.
4:07 pm on Apr 28, 2010 (gmt 0)

Moderator from US 

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Dec 10, 2005
posts:5710
votes: 88


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