sql optimize search many-many

Question

usertable
----
id, username

grouptable
----
id, groupname

group_user
--------
uid, gid

books
----
id, groupname

Input parameters: groupname, username
output: list of books

Is it possible to use 1 sql statement to get list of books when username is inside groupname

Question 2: any good book to recommand to master complex sql statement..

Answer 1

Hi,

This query gives list of books by user parametar

SELECT b.id,
FROM usertable u
     INNER JOIN group_user gu ON gu.uid = u.id
     INNER JOIN grouptable g ON g.id= gu.gid
     INNER JOIN books b ON b.groupname = g.groupname 
WHERE u.username = @user_name

Also i think if you have the group name you can use

SELECT b.id,
FROM grouptable g 
     INNER JOIN books b ON b.groupname = g.groupname 
WHERE g.groupname = @group_name

Bus having select on both parameters i think is not very good think. This query will get list of books for user group name

SELECT b.id,
    FROM usertable u
         INNER JOIN group_user gu ON gu.uid = u.id
         INNER JOIN grouptable g ON g.id= gu.gid
              AND g.group_name = @group_name
         INNER JOIN books b ON b.groupname = g.groupname 
    WHERE u.username = @user_name