Query help - Join

SQL++
1

Hi,

I have the following schemas and trying to get the count.
Book
{ type : '_Book', name: 'Name of the Book' }
StudentBooks
{ type : '_Student_Book', book : {_id: '{_Book.id}', name: ''} }

Now, I am trying to get the total count of Books that students have per type _Book. We tried this with an ANSI join but it is not returning count 0’s. Any suggestions on what kind of join I should use?

2 
SELECT b.name, count(s) AS cnt
FROM default AS b
LEFT JOIN default AS s ON  s.type = '_Student_Book' AND s.book.name = b.name
WHERE b.type = '_Book'
GROUP BY b.name;
3

Thanks. Is there any way the same can be achieved in v5.1 or v4.6?

4

But this query is not giving the _Book which doesn’t have any _Student_Book record.

5

No. You should try with v6.0 and it should give right results

6

I understand that this works with 6.0 but we are running on 4.6 and 5.1 currently for two different products and trying to see if I can achieve the same with a single query.

7

I the Book.id belongs document key of Book

CREATE INDEX ix1 ON  default(book._id);
CREATE IDEX ix2 ON default(name) WHERE type =  '_Book'

SELECT b.name, count(s.book._id) AS cnt
FROM default AS b
LEFT JOIN default AS s ON  KEY  s.book._id FOR b
WHERE b.type = '_Book' AND b.name IS NOT NULL
GROUP BY b.name;
8

Thanks. It worked. Also realized that book._id cannot be part of another document other than type _Student_Book. I guess I can live with that for now. Really appreciate your help and quick response.

9

Can any one tell me whether this course is useful or not. http://bit.ly/2ZyDFwx