Solved

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

Posted on 2004-08-10
12
1,699 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
Comment Utility
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
Comment Utility
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 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 200 total points
Comment Utility
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
 
LVL 17

Expert Comment

by:BillAn1
Comment Utility
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
Comment Utility
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
Comment Utility
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
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
Comment Utility
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
Comment Utility
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 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
Comment Utility
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

763 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now