I my database has people and their memberships. A person can have multiple memberships. Right now, the query only returns people with the membership id of 10, which is correct but it ignores their other memberships. How can I make the query only list people with a membership id of 10 but also list their other memberships? Is there a way to say mebership id contains 10?
[sql] SELECT GROUP_CONCAT(membership_name ORDER BY M.membership_id SEPARATOR ', ') AS membership_name, P.people_id, people_fname, people_mname, people_lnameFROM membership M, people_membership PM, people P,people_title PTWHERE M.membership_id=PM.membership_id AND P.people_id=PM.people_id AND P.people_id=PT.people_id AND M.membership_id=10 GROUP BY P.people_idORDER BY P.people_lname ASC [/sql]
Where / And - Not so specific
Moderator: General Moderators
Re: Where / And - Not so specific
First, I wanted to help you 4-5 hours ago, but when I saw this "table as t" I decided not to. In my opinion, if you want help you should make your query as clear as possible.
Anyway... I see that nobody answers you, so...:
[sql]SELECT GROUP_CONCAT(membership.name ORDER BY membership.id SEPARATOR ', ') AS membership_name, people.id, people.fname, people.mname, people.lname, COUNT(IF(membership_id=10, 1, NULL)) AS required_membership_id_presenceFROM peopleINNER JOIN people_membership ON people_membership.FK_people_id = people.idINNER JOIN membership ON people_membership.FK_membership_id = membership.idGROUP BY people.idHAVING required_membership_id_presenceORDER BY people.lname ASC[/sql]
* I wrote it down the way I think it's more appropriate to name tables/columns and to format a SQL query.
Anyway... I see that nobody answers you, so...:
[sql]SELECT GROUP_CONCAT(membership.name ORDER BY membership.id SEPARATOR ', ') AS membership_name, people.id, people.fname, people.mname, people.lname, COUNT(IF(membership_id=10, 1, NULL)) AS required_membership_id_presenceFROM peopleINNER JOIN people_membership ON people_membership.FK_people_id = people.idINNER JOIN membership ON people_membership.FK_membership_id = membership.idGROUP BY people.idHAVING required_membership_id_presenceORDER BY people.lname ASC[/sql]
* I wrote it down the way I think it's more appropriate to name tables/columns and to format a SQL query.
There are 10 types of people in this world, those who understand binary and those who don't
Re: Where / And - Not so specific
Thank you for your help. What is the FK prefix?
[sql]INNER JOIN membership ON people_membership.FK_membership_id = membership.id[/sql]
[sql]INNER JOIN membership ON people_membership.FK_membership_id = membership.id[/sql]
Re: Where / And - Not so specific
FK = Foreign Key
This way you know a field is a reference to a field of another table.
This way you know a field is a reference to a field of another table.
There are 10 types of people in this world, those who understand binary and those who don't