CODEWITHSUNDEEP
javasql
Yangrui
Yangrui

Reputation: 1247

Nested SQL Generator?

I am trying to create a nested SELECT SQL statment. I store all value and id and want to select rows that satisfy multiple values. How can I generate a SELECT statement using Java? For example, 

ID VALUE
1  RED
2  BIG
1  SMALL
1  SMOOTH
2  TALL

.....

To select an item that is both red and small the statement would be:

SELECT * 
FROM table 
WHERE table.value = RED AND 
       id IN (SELECT * 
              FROM table 
              WHERE table.value = SMALL AND id IN (...))

Upvotes: 1

Views: 174

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

Here is a general way to approach this:

select id
from t
group by id
having max(case when value = 'Red' then 1 else 0 end) = 1 and
       max(case when value = 'Small' then 1 else 0 end) = 1

In other words, membership in the set becomes a clause in the having statement. These can be both inclusion and exclusion (use = 0 instead of = 1) and optional (use or instead of and).

Upvotes: 0

John Woo
John Woo

Reputation: 263713

This type of problem is called Relational Division

SELECT ID
FROM tableName
WHERE VALUE IN ('SMALL','SMOOTH')
GROUP BY ID
HAVING COUNT(*) = 2

the query above will result 1 since the ID contains both records.

If no unique constraint was enforce on value for every ID, DISTINCT is required.

SELECT ID
FROM tableName
WHERE VALUE IN ('SMALL','SMOOTH')
GROUP BY ID
HAVING COUNT(DISTINCT VALUE) = 2

OTHER(s)

Upvotes: 1

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171401

select ID
from MyTable
where VALUE in ('RED', 'SMALL')
group by ID
having count(distinct VALUE) = 2

SQL Fiddle Example

Results:

| ID |
------
|  1 |

Upvotes: 0

Related Questions