Where / And - Not so specific

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
psurrena
Forum Contributor
Posts: 355
Joined: Thu Nov 10, 2005 12:31 pm
Location: Broolyn, NY

Where / And - Not so specific

Post 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]
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Where / And - Not so specific

Post 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.
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
psurrena
Forum Contributor
Posts: 355
Joined: Thu Nov 10, 2005 12:31 pm
Location: Broolyn, NY

Re: Where / And - Not so specific

Post 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]
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Where / And - Not so specific

Post by VladSun »

FK = Foreign Key
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
Post Reply