Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Mar 2005
    Posts
    71

    Unanswered: multi field select using IN...

    here is what i am wanting to do but is it possible to use multi fields with an IN statement?

    Code:
    SELECT     *
    FROM         usmastf
    WHERE     usm_book, usm_acct IN
                              (SELECT     usac_book, usac_acct
                                FROM          uscommf
                                WHERE      usac_code = 'O/W')

  2. #2
    Join Date
    Mar 2005
    Posts
    71

    Never Mind sorry

    i finally found it

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Not with an IN function. Use a standard JOIN instead:
    Code:
    SELECT	usmastf.*
    FROM	usmastf
    	inner join uscommf
    		on usmastf.usm_book = uscommf.usac_book
    		and usmastf.usm_acct = uscommf.usac_acct
    WHERE	uscommf.usac_code = 'O/W'
    If it's not practically useful, then it's practically useless.

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

  4. #4
    Join Date
    Mar 2005
    Posts
    71

    Just curious....

    What does the pipe sign actually represent in the following

    Code:
    select * from usmastf
    where ((usm_book | usm_acct) in (select usac_book | usac_acct from uscommf where usac_code = 'O/W'))

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    The pipe character represents a boolean "OR" evaluation, but I don't think it is syntactically correct in your statement.
    If it's not practically useful, then it's practically useless.

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

  6. #6
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    in particular, the vertical bar means *bitwise* or, not logical or (same as in C/C++).

    logical or is just the word "or" in sql (this would be || in C/C++).

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    oh you silly microsoft peoples

    hicpics, i think you're thinking of two vertical pipes, which is the sql standard for concatenation

    ... where usm_book||usm_acct in (select usac_book||usac_acct from ...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by r937
    oh you silly microsoft peoples
    I can't imagine why people would assume that the post was talking about Microsoft SQL in this forum!

    You have a good point, and I thought of that myself, but we need to assume that the poster really is working with Microsoft SQL when they post a question here. If they are using a different SQL or SQL-like language then one of the moderators ought to move the post for them.

    -PatP

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I too thought of concatenation, as I am unfortunately mired in the Oracle world at the moment. But the poster asked about the meaning of a single pipe, not double-pipes.
    If it's not practically useful, then it's practically useless.

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

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    actually the original poster never said "single pipe" -- and he was clearly using it as a concatenation operator in post #4

    and being mired in oracle would only be an interesting coincidence, the double pipes are standard sql -- are you suggesting that oracle peoples have a better understanding of standard sql than microsoft peoples?

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

  11. #11
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    can one of you educate me? I have never heard of | being used as a concatenation operator. Do you mean string concatenation? Every reference in BOL I can find about | says it's bitwise or.

    the | means bitwise or in most languages I am familiar with (except befunge, but I can't say I'm that familiar with it )

    and this don't work at all: select 'asdf' || 'qwerty'

    Last edited by jezemine; 10-29-06 at 23:52.

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Double-pipes concatenation is not allowed syntax for SQL Server.

    You are trying to say "If A OR B is in C OR D", but this is a logical comparison and not a bitwise "OR" which is what the pipe character stands for. So your syntax will not work.

    Did you try the code I posted?
    If it's not practically useful, then it's practically useless.

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

  13. #13
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Who uses a single pipe as a conact operator?

    MySQL?

    I doubt it....

    Oracle and DB2 are ||

    I always thought + was a silly conact operator for chars
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  14. #14
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    Quote Originally Posted by Brett Kaiser
    I always thought + was a silly conact operator for chars
    really? makes perfect sense to me.

    ||, on the other hand, makes no sense to me as concat. || has always meant logical or to me. But I was raised on C, not sql.

    If you were implementing a string class in C++ and overloaded || to mean string concat, I'd have to, well, I won't say it.

    EDIT: actually, you should never overload || for any reason, but that's a separate issue.

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by jezemine
    really? makes perfect sense to me.
    oh?

    what is the result of SELECT 2 + '2' then?

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

Posting Permissions

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