Page 1 of 1

Where / And - Not so specific

Posted: Tue Sep 16, 2008 11:38 am
by psurrena
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]

Re: Where / And - Not so specific

Posted: Tue Sep 16, 2008 5:26 pm
by VladSun
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.

Re: Where / And - Not so specific

Posted: Wed Sep 17, 2008 10:52 am
by psurrena
Thank you for your help. What is the FK prefix?

[sql]INNER JOIN    membership ON people_membership.FK_membership_id = membership.id[/sql]

Re: Where / And - Not so specific

Posted: Wed Sep 17, 2008 11:05 am
by VladSun
FK = Foreign Key
This way you know a field is a reference to a field of another table.