Results 1 to 12 of 12
  1. #1
    Join Date
    Oct 2008
    Posts
    24

    Unanswered: A little query problem help

    Trying to solve this query word problem...

    Use the UNION operator to generate a result set consisting of two columns from the Vendors table: VendorName and VendorState. If the vendor is in California, the VendorState value should be “CA”; otherwise, the VendorState value should be “Outside CA.” Sort the final result set by VendorName.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    HOMEWORK!! first!!1!!!

    and a most appropriate assignment for someone called Datanalysis to tackle

    the first question should be: why does it have to use UNION?

    and the answer, of course, is: because it's a homework assignment!!!

    okay, here's a hint: a UNION query contains more than one SELECT, and you appear to have more than one condition that needs to be accommodated -- two, in fact

    does that get you started?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    ...or he could put both criteria into a single select statement and toss this on the end:
    Code:
    UNION select [columnlist] where 1 = 0
    Voila!
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Oct 2008
    Posts
    24
    Yes my Screen name doesnt exactly match my expertise level, but I think I may have an idea from your hints...hope I can figure it out.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    See, his first name is actually Datana, and his last name is Lysis....

    Stop back if you get stumped, Datana. We'll help you through it.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Just like your first name is Blin and your last name is D'Man
    George
    Home | Blog

  7. #7
    Join Date
    Oct 2008
    Posts
    24
    yes im stumped...I cant figure how to convert a value in the query such as california to CA

  8. #8
    Join Date
    Feb 2009
    Posts
    2
    Quote Originally Posted by Datanalysis
    yes im stumped...I cant figure how to convert a value in the query such as california to CA
    I don't think that's what the question is asking, although it may be related (for the "Outside CA" part). I think you'll find that the database has some VendorState values that are: CA
    so you need to write a SELECT statement that identifies those (very simple) and then UNION that with another SELECT statement that selects those VendorState values that are <> 'CA'

    The trick (as i am a novice also) is how to make the VendorState column report back Not in CA
    for the rows where VendorState <> 'CA'

  9. #9
    Join Date
    Feb 2009
    Posts
    2
    You'll need to figure out the WHERE clause on the second select statement to narrow down which fields' text gets replaced with 'Outside CA'

    SELECT <stuff>
    FROM <stuff>
    WHERE <stuff>
    UNION
    SELECT <stuff>, 'Outside CA' AS VendorState
    FROM <stuff>
    WHERE <stuff>
    ORDER BY <stuff>

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by georgev
    Just like your first name is Blin and your last name is D'Man
    It's French. You got a problem with that, monsieur?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Can you give us the DDL (CREATE TABLE ..) scripts and some sample data. It will be easier for us to help you further.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by blindman
    It's French. You got a problem with that, monsieur?
    Non, you D'Man!
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •