Solved

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

Posted on 2004-08-10
12
1,798 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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
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

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
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
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

630 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