Solved

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

Posted on 2004-08-10
12
1,760 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 50 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 200 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
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

Business Impact of IT Communications

What are the business impacts of how well businesses communicate during an IT incident? Targeting, speed, and transparency all matter. Find out more in this infographic.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
TSQL remove duplicates from different columns 14 52
Remove () 10 41
Please explain the difference between EXCLUDE, INTERSECT and JOIN 7 52
SQL query 45 41
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

751 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