Results 1 to 12 of 12
  1. #1
    Join Date
    Jul 2003
    Posts
    24

    Unanswered: join with my values (not with table)

    Hey,

    I want to select all item which their height and their width are in the following columns input:
    2,3
    4,5
    and etc...

    I can write query like this:
    select * from item where (height = 2 and width = 3) or (height= 4 and width=5) and ...

    const - performance.(cannot be prepare statement, re-use plan ...)

    another option is to use temp table - but its not standart.

    Are there additonal options?

    Thank you

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I'm not quite sure what your question is.
    You would not want a plan to be reused here - depending on what you submit, the optimal plan might be a scan or a seek (assuming height and\ or width are indexed).

    What is your problem with the query?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jul 2003
    Posts
    24

    Explain

    Reuse- i mean that when you use preparestatement it compile the plan , and cache the query plan.

    My query is on 2000 items (each time), As far as i know query with 2000 OR is not recommended.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    How many rows in the table?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jul 2003
    Posts
    24

    more than 1,000,000

    more than 1,000,000

  6. #6
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    avihai, without know more about your process, it sounds like the best option is to put the data into a table (temporary or not). Then the query would be a simple:
    Code:
    SELECT columns
    FROM table-data D
       , table-values V 
    WHERE D.HEIGHT = V.HEIGHT
      AND D.WEIGHT = V.WEIGHT
    With the appropriate indexes on the columns.

    It depends on how you are currently getting 2000 pairs of height/weight into the SQL and whether loading a table is a problem.

  7. #7
    Join Date
    Jul 2003
    Posts
    24
    I have the 2000 pairs as a result from another process,
    so you said to create temp table, insert the pairs values to the temp and than join?

    but does temp table is a standart?

  8. #8
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    What is it exactly that you want?
    Code:
    select * 
    from item 
    where (height = width - 1)  and ...
    Is conform your example, just wonder if this is what you want.
    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

  9. #9
    Join Date
    Jul 2003
    Posts
    24

    No, this is not what i mean

    In table item i have two columns height, width

    I need all the items which their width = 4 and height =7 or width = 2 and height =10 ....

    I have a set of 2000 height and width couple. {(h,w),(h1,w1),(h2,w2)...,(h1000,w1000)}

    I am asking for the best way to query all the item which their height is equal to height and width is equal to width.

  10. #10
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Then I would use Stealth_DBA's suggestion.
    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

  11. #11
    Join Date
    Jul 2003
    Posts
    24
    My question is how to i load my values to the sql?

    I dont have table with those values. i have them from the application.

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by avihai
    I dont have table with those values.
    then you should make one

    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
  •