Q (kdb): nested where query

Question

What are the ways to in Q to use the results of a nested query in a where clause?

I'm looking for something similar to SQL statement.

select from food where type_id in (
    select type_id from types where type_name = "fruit"
)

Answer 1

select from food where type_id in (exec type_id from types where type_name like "fruit")

Your query was almost correct apart from what you passing to the in predicate and use the like function for string equality. You are passing a table when it only accepts a list. To send the query as a list I use exec which does the job.

Answer 2

while that's the direct answer to your question, the best way do this is probably foreign keys:

q)types:([type_id:`apple`orange`cucumber]type_name:`fruit`fruit`vegetable)
q)food:([type_id:`types$`apple`orange`cucumber]price:3?2.)
q)meta food
c      | t f     a
-------| ---------
type_id| s types  
price  | f        
q)select from food where type_id.type_name=`fruit
type_id| price    
-------| ---------
apple  | 0.4593231
orange | 1.383906 
q)

Answer 3

Another way of doing it:

select from food where type_id in (select type_id from types where type_name like "fruit")[`type_id]