SQL: return true/false if multiple values exists or not in a subquery

Question

I have a table with two columns that are foreign keys (think in userID(int) : orderID(int) for example), and I have to know if orders 2, 3, 4, 5 exists for a user ID, in a where clause of a big query.

I need to optimize my database

SELECT table1.myrow FROM table1, table2
WHERE table1.myrow = table2.myrow
AND 1 IN (SELECT myRoww from table2 WHERE table2.id = table1.myrow)
AND 2 IN (SELECT myRoww from table2 WHERE table2.id = table1.myrow)
AND 3 IN (SELECT myRoww from table2 WHERE table2.id = table1.myrow)

I want to do something like this:

 AND (SELECT * from mytable) IN (SELECT myRoww from table2 WHERE table2.id = table1.myrow)

How can I determine if my multiple value list exists for an ID? the rows i'm requesting are relations that consist in only a table with two foreign keys.

My relations:

Need to know if CONVOCATORIA_SECTOR have X relations for a CONVOCATORIAS(id_bdns_Conv)

Can you please include the actual tables you are using along with some sample data? — Tim Biegeleisen
– Tim Biegeleisen, Commented Nov 21, 2019 at 17:11

forpas · Accepted Answer · 2019-11-21 17:16:13Z

3

You can do it by grouping by userid and a condition in the HAVING clause:

select userid
from tablename
where orderid in (2,3,4,5)
group by userid
having count(distinct orderid) = 4

This will select all the userids for which there exist the orderids 2, 3, 4 and 5.

answered Nov 21, 2019 at 17:16

forpas

165k10 gold badges51 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Enrique García Over a year ago

I don't understand the having you added

Tim Biegeleisen Over a year ago

Please add sample data to your question to make it complete.

forpas Over a year ago

The HAVING clause guarantees that the distinct orderids for each user are 4 and since the WHERE clause restricts the orderids to 2,3,4,5 then these 4 are the ones that exist for the user.

Enrique García Over a year ago

This not solves my problem because I need to add this to "where" clause for selecting or not the rows.

forpas Over a year ago

You can add it to a WHERE clause like: WHERE somecolumn IN (<query here>).

jgalaso · Accepted Answer · 2019-11-21 17:14:38Z

0

as an ugly answer, you can concatenate both columns like this

AND (SELECT column1||column2 from mytable) IN (SELECT column1||column2 from table2 WHERE table2.id = table1.myrow)

is not the best way but it works

answered Nov 21, 2019 at 17:14

jgalaso

224 bronze badges

Collectives™ on Stack Overflow

SQL: return true/false if multiple values exists or not in a subquery

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related