You Dream it...We Build it...
ColdFusion Muse
Multiple Columns in a Sub Select??
Selecting more than 1 column in a sub-select is possible. I learned this tip from Jochem van Dieten (who knows more about SQL than anyone I know) on a recent CF-Talk thread. According to Jochem you can use the following Syntax:
<Cfquery ... >
SELECT * FROM table
WHERE (x, y) IN
(SELECT a, b FROM otherTable)
</CFQUERY>
...Now ya gotta admit that's pretty neat!
SELECT * FROM table
WHERE (x, y) IN
(SELECT a, b FROM otherTable)
</CFQUERY>
Note: This syntax will not work on every db server. It will not work on MS SQL server for example. See the notes below for more detail
Blog provided and hosted by CF Webtools. Blog Sofware by Ray Camden.
---
IN
Determines if a given value matches any value in a subquery or a list.
Syntax
test_expression [ NOT ] IN
(
subquery
| expression [ ,...n ]
)
Arguments
test_expression
Is any valid Microsoft® SQL Server™ expression.
subquery
Is a subquery that has a result set of one column. This column must have the same data type as test_expression.
expression [,...n]
Is a list of expressions to test for a match. All expressions must be of the same type as test_expression.
---
Notice the fact that the subquery has a result set of ONE column.
I was hoping you would chime in before I had to go slogging through documentation :) PostgreSQL and MySQL are probably a "go" then... I wonder about Oracle?
SELECT
*
FROM
table
WHERE
x IN ( SELECT a FROM otherTable ) AND
y IN ( SELECT b FROM otherTable )
You are correct as far as it goes. But the advantage of doing it in one statement would be a much leaner execution plan. The engine would have to apply a single WHERE clause to a subset - instead of 2 separate where clauses to 2 subsets.
It has to be said though, that I hardly ever need to do anything just like this. I don't often have two relationships between the same tables defined, and therefore have no need to build a query like this as well. And IF I need something similar, I normally need the output from 'otherTable' as well:
SELECT
*
FROM
table INNER JOIN otherTable ON ( x = a AND y = b )
Which does *not* result in the same set of course, just stating that both relations need to 'comply'.
FROM table
WHERE
x IN ( SELECT a FROM otherTable ) AND
y IN ( SELECT b FROM otherTable )
This is not the same as the posted query. In this query a and b are not required to be from the same row. The correct translation would be:
SELECT *
FROM table INNER JOIN otherTable ON
( table.x = otherTable.a AND table.y = otherTable.b)
This would be the recommended way of writing the query anyway because it is easier on the optimizer. You frequently need this type of query when you have composite primary keys.
BTW The SQL standard is available for free in some libraries, such as the library of Delft University of Technology.
SELECT *
FROM
table t
INNER JOIN otherTable ot
ON t.x = ot.a
AND t.y = ot.b
Yes .... it is simple to do with joins, but it also provides a simple example, which is the point.
touche. The an example was a simple one. For more complex queries you can dervied table to get the results. Also you can do the smeaky method to use one or more columns
SELECT *
FROM table t
WHERE
t.pk IN (
SELECT ot.fk
FROM othertable ot
WHERE
t.x = ot.a
AND t.y = ot.b
)
Thanks for the very cool tip!
Thanks for verifying.
-Mark
SLECT * FROM table
WHERE (x, y) IN
(select x, max(y) FROM table GROUP BY x);
Oracle is complaining with the ORA-00920: invalid relational operator.
select a.* from table a inner join
(select x, max(y) as y from table group by x) b
on a.x = b.x and a.y = b.y;
I've used this on MS-SQL 2000. Haven't verified it works on others.
select t1.a, t1.b from t1
where t1.a, t1.b not in
(select t2.a, t2.b) --complex inline view
select a.* from table a inner join
(select x, max(y) as y from table group by x) b
on a.x <> b.x and a.y <> b.y;
It should be possible to substitute the 'and' operator by 'or' if you you want any of the conditions to cause an exclusion.
SELECT * FROM table
WHERE (x+y) IN
(SELECT x+max(y)
FROM table
GROUP BY x);
SELECT *
FROM table
WHERE
x IN ( SELECT a FROM otherTable ) AND
y IN ( SELECT b FROM otherTable )
the correct one is as below...and the below did work for me on oracle..andy said it did not work for him but i just executed now now...
SLECT * FROM table
WHERE (x, y) IN
(select x, max(y) FROM table GROUP BY x);
in the where put field = null like this:
SELECT
A.field1,
A.field2
FROM
B RIGHT OUTER JOIN
A ON B.field1 = A.field1 AND
B.field2 = A.field2
WHERE
(B.field1 IS NULL) AND
(B.field1 IS NULL)
saludos desde Bog. Col.
I try to link 2 tables using two columns and one condition but I can't make it work and I didn't get how you used the group by function
Here's my code:
select *
from Tbl_Observers
where
Tbl_Observers.LocationID in (select Tbl_Field.LocationID from Tbl_Field where Tbl_Observers.LocationID='CO')
and Tbl_Observers.SurveyID in (select Tbl_Field.SurveyID from Tbl_Field)
and Tbl_Observers.ObserverCode='H_Edwards'
Can you help me please
Delete from tbl1
where (col1, col2) in
(select col1, col2 from tbl1 where.....)
Is a really useful construct.