Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2004-08-10
12
Medium Priority
?
1,865 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 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

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…
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
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

670 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