Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2003
    Posts
    8

    Unanswered: Need help with a Query

    I have a table with 2 columns.
    I need to write a query to which I can pass in parameters (col1) that will return me col2 that has all those parameter links.

    For examples, in the a table below:

    col1 col2
    -- ---
    1 1
    2 2
    1 2
    3 3
    1 4
    2 5
    1 5


    I need to find records where Col2 has Col1 values as 1 and 2


    The result from the above table will be 2 and 5 since they are the rows that correspond to values of 1 and 2 in Col1.

    I tried to write a simple query like:

    select col2 from temptag where col1 = 2 and col2 in (select col2 from temptag where col1 = 1)

    The problem with above query is that it will be slow. Also, this will be take care if I query for col1 as 1 and 2. But if I have more numbers parameters (more col1 values to search on), then I would have to modify my query every time.
    I believe self join will be helpful to resolve this SQL problem but I can't seem to find a solution to it.

    Can someone please help me?
    Last edited by Oregonian; 01-23-08 at 20:19.

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    to slow? create an index.

    multiple parameters? look at the IN keyword or perhaps an OR.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Oct 2003
    Posts
    8
    If you see my query, I have used "in" in a way. That is not my problem. The problem is that the query will have to change depending on how many parameters are there if its written the way I wrote above.
    There must be a more efficient way of writing above query.

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    IN has more than one use. Google these words or read your textbook you got with class "SQL Server Books Online IN".
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Code:
    --Set up the data
    create	table MyTable (Col1 int, Col2 int)
    
    insert into MyTable (Col1, Col2)
    select 1, 1
    union select 2, 2
    union select 1, 2
    union select 3, 3
    union select 1, 4
    union select 2, 5
    union select 1, 5
    
    --Create a list of search values
    declare	@SearchValues table (SearchValue int)
    insert into @SearchValues (SearchValue) Values (1)
    insert into @SearchValues (SearchValue) Values (2)
    
    --Search for hits
    select	Col2
    from	MyTable
    		inner join @SearchValues SearchValues on MyTable.Col1 = SearchValues.SearchValue
    group by Col2
    having count(*) = (select count(distinct SearchValue) from @SearchValues)
    
    --Clean up
    drop table MyTable
    You'll probably want to accept your search values as a comma-separated string and them parse it into the search table. You can also drop the distinct clauses if you constraints support 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
    Oct 2003
    Posts
    8
    Thanks BlindMan
    That's exactly what I was looking for...you made my day

Posting Permissions

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