Using a query as part of where clause


#1

Hi,

I amnot sure I am defining this query correctly. Can you please look at it and tell me what I am missing:

select * from P pn
LEFT JOIN B at ON KEYS pn.bkId
where pn.bk in ( select cu.bk from B cu USE KEYS pn.bookId where cu.d == ‘LDN’);

I am trying to filter the B’s so that I only get P’s for B’s that meet the criteria. In sql you might write something like

select * from P where P.bk in ( select B.bk from B where B.d==‘LDN’)

Teh pn.bookId has the B document id.

Best


#2

Here is the sql that returns no entries even though there are matches

select * from Positions position
LEFT JOIN Books account ON KEYS position.bookId
where position.book in ( select cu.book from Books cu USE KEYS position.bookId); where cu.domain == ‘LDN’);


#3

Sorry paste error
select * from Positions position
LEFT JOIN Books account ON KEYS position.bookId
where position.book in ( select cu.book from Books cu USE KEYS position.bookId where cu.domain == ‘LDN’);


#4

Hey @lynchpin1,

Can you add some sample documents to this thread and the expected query results?

It will give us something better to go off of.

Best,


#5

I added the keyword RAW to your subquery: