CODEWITHSUNDEEP
phpmysqlsql
Andy Mills
Andy Mills

Reputation: 135

Correct SQL Query for Accessing Groups available to User

I have a series of tables set in the following way (they have been simplified for readability):

user
- id
- name

user_has_oi_relationship
- user_id
- oi_relationship_id

oi_relationship
- id
- description

chat_group_has_oi_relationship
- chat_group_id
- oi_relationship_id

chat_group
- id
- name
- description

Each entry in the 'user' table has a series of OI relationships attached to it, as defined in the 'user_has_oi_relationship' table. Each chat group also has a series of OI relationships attached to it, as defined in the 'chat_group_has_oi_relationship' table.

What I'd like to do is select the available chat groups available for a particular user, as defined by their user ID, based on their OI relationships.

I'm normally pretty good with joins and such, but this one is stumping me. I keep getting all the groups returned!

Upvotes: 0

Views: 58

Answers (3)

Barmar
Barmar

Reputation: 781716

I think this should do it:

SELECT cg.*
FROM chat_group AS cg
JOIN chat_group_has_oi_relationship AS cgr ON cg.id = cgr.chat_group_id
JOIN user_has_oi_relationship AS ur ON ur.oi_relationship_id = cgr.oi_relationship_id
JOIN user AS u ON ur.user_id = u.id
WHERE u.name = 'John Smith'

If not, please make a sqlfiddle with sample data.

Upvotes: 1

Marshall Tigerus
Marshall Tigerus

Reputation: 3764

Select cg.name, cg.description FROM chat_group cg
INNER JOIN chat_group_has_oi_relationship coir
ON cg.id = coir.chat_group_id
INNER JOIN user_has_oi_relationship uoir
ON coir.oi_relationship_id = uoir.oi_relationship_id
WHERE uoir.user_id = ?

Tweak this to fit what data you want returned. If you need to delimit by user name, add another join for the user table

Upvotes: 0

vav
vav

Reputation: 4694

Ok, let start with user_has_oi_relationship for the user:

--query 1
select oi_relationship_id from user_has_oi_relationship where user_id =:user_id

Now go to chat_groups that have that oi

--query 2
select chat_group_id from chat_group_has_oi_relationship
where oi_relationship_id in (
--query 1
select oi_relationship_id from user_has_oi_relationship where user_id =:user_id
)

And finally getting names of the groups:

select id, name from chat_group
where id in (
--query 2
select chat_group_id from chat_group_has_oi_relationship
where oi_relationship_id in (
--query 1
select oi_relationship_id from user_has_oi_relationship where user_id =:user_id
)
)

Readability of sql statements is important, so you should write them the way you think about it. DBs are smart to optimize these queries into something that will be run fast enouth. Only when it starts to be a performance bottleneck you need to "consider rewriting".

BTW, thanks for the shortening your db structure for the question.

Upvotes: 3

Related Questions