Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Where (Col1, Col2) in (select...

Posted on 2004-08-10
12
Medium Priority
?
1,897 Views
Last Modified: 2008-03-06
I wrote a SQL that would work with Oracle, but it is a speciality because in SQL Server there is a syntax error.
I need to retrieve all pairs of test_number, id_numeric where some of them are meeting a certain condition.

If I write:

select test_number, id_numeric from measures where ...condition...

I don't have every row having the same test_number, id_numeric pair. In Oracle I would write:

select * from measures
where  (test_number, id_numeric) in
(select test_number, id_numeric from measures where ...condition...

How would you write this SQL?
Thanks.
0
Comment
Question by:ornicar
  • 4
  • 4
  • 3
  • +1
12 Comments
 
LVL 9

Author Comment

by:ornicar
ID: 11761753
This one would work, obviously,

select * from measures
where  test_number + ' ' + id_numeric in
(select test_number + ' ' + id_numeric from measures where ...condition...

but as there are more than 6 moi records in this table, I am afraid that it would take ages as there is no index used.

0
 
LVL 17

Assisted Solution

by:BillAn1
BillAn1 earned 200 total points
ID: 11761758
try this :

select * from measures m1
where  exists
(select 1 from measures m2 where m1.test_number = m2.test_number and m1.id_numeric = m2.id_numeric and ...condition...)

0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 800 total points
ID: 11761864
select measures.*
from measures
JOIN (select test_number ,  id_numeric from measures where ...condition... ) as CL
ON CL.test_number = measures.test_number
AND CL.id_numeric = measures.id_numberic

CHeers

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 17

Expert Comment

by:BillAn1
ID: 11762017
The only problem with your answer angelIII is that if there is more tha 1 record in the table with the same values for test_number and measures_id you will return n*n records
e.g. if there are2 you will return 4, if 3 then 9 etc
0
 
LVL 14

Expert Comment

by:Jan Franek
ID: 11762021
Slightly modified angelIII's :

select M1.*
from measures M1
JOIN measures M2
ON M1.test_number = M2.test_number
AND M2.id_numeric = M2.id_numberic
where ...condition...

in ...condition... use M2. prefix
0
 
LVL 9

Author Comment

by:ornicar
ID: 11762083
Thanks. I tried both of your suggestions and effectively, AngelIII's returns more rows. But the advantage is that it is the fastest. I hesitate between the two. I need this SQL to update some flags in the rows and perhaps I'll choose the fastest.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 11762088
BillAn1, you are basically correct, but the same "problem" is in oracle, and subject to another question/thread :-)
Anyway, the field names suggest that the values will be unique...
CHeers

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 11762120
If you use it for updating, it shall be no problem, because if you get "duplicate" rows, in fact they are the same underlying rows. You might try to add DISTINCT to you query, but when doing update it doesn't matter
Cheers
0
 
LVL 9

Author Comment

by:ornicar
ID: 11762318
I choose angelIII's as the answer because it is the fastest and for my update I don't need the exact amount of rows. BillAn1's can be considered as more correct as it returns the rows only once.
Thanks for your help.
0
 
LVL 17

Expert Comment

by:BillAn1
ID: 11762328
No,the oracle version ornicar provided, or the version I provided, won't give any duplicates, because it is not a join of the table back on itself, it is a "select * from mytable WHERE .....", so you won't get any duplicate rows. If you join the table to itself on a non-unique col, you get duplicates. Even if it is unique, it is less efficient to do the join.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 11762928
BillAn1, I see the difference in the results, indeed the join can result in duplicated rows.
However, your suggestion also performs a join, which basically could be resolved by the optimizer to give the same/similar execution plan. IMHO
CHeers
0
 
LVL 17

Expert Comment

by:BillAn1
ID: 11763607
In practical terms, yes the optimiser will often end up choosing a similar execution plan - particularly, if the data is unique, there will probably be no performance gain, True :-)
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how the fundamental information of how to create a table.

971 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question