Three Table Query Help

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

Three Table Query Help

Post by psurrena »

feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


I have three tables:
	- people, has a persons info
	- membership, has a list of memberships
	- people_membership, which has people_id and membership_id
	
Here's what I want to happen:
- Get the persons info from PEOPLE
- Use the ID from PEOPLE and check what MEMBERSHIP ID's are associted with it in PEOPLE_MEMBERSHIP
- Match the MEMBERSHIP_ID's in the MEMBERSHIP table
* A person can have multiple memberships

[syntax="sql"]
SELECT people.people_id, people.people_fname, people.people_mname, people.people_lname, people_membership.membership_id
FROM people,people_membership
WHERE people.people_title=$tid
AND people.people_id=people_membership.people_id
	(SELECT membership.membership_name 
	FROM membership
	WHERE membership.membership_id=people_membership.membership_id)
Any help would be greatly appreciated.


feyd | Please use[/syntax]

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

What's not working in the current query?
User avatar
psurrena
Forum Contributor
Posts: 355
Joined: Thu Nov 10, 2005 12:31 pm
Location: Broolyn, NY

Post by psurrena »

I get this error:
"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT membership_name FROM membership WHERE membership.membership_id=peopl' at line 5"

I've sort of had the query working before using left joins but it returned each record a ton of times.
User avatar
psurrena
Forum Contributor
Posts: 355
Joined: Thu Nov 10, 2005 12:31 pm
Location: Broolyn, NY

Post by psurrena »

Here are the tables from the DB

Code: Select all

mysql> describe people;
+-----------------+--------------+------+-----+---------+----------------+
| Field           | Type         | Null | Key | Default | Extra          |
+-----------------+--------------+------+-----+---------+----------------+
| people_id       | int(3)       | NO   | PRI | NULL    | auto_increment |
| people_fname    | varchar(255) | NO   |     |         |                |
| people_mname    | varchar(1)   | NO   |     |         |                |
| people_lname    | varchar(255) | NO   |     |         |                |
| people_title    | int(1)       | NO   |     |         |                |
| people_des      | text         | NO   |     |         |                |
| people_location | varchar(255) | NO   |     |         |                |
+-----------------+--------------+------+-----+---------+----------------+

mysql> describe membership;
+-----------------+-------------+------+-----+---------+----------------+
| Field           | Type        | Null | Key | Default | Extra          |
+-----------------+-------------+------+-----+---------+----------------+
| membership_id   | int(2)      | NO   | PRI | NULL    | auto_increment |
| membership_name | varchar(25) | NO   |     |         |                |
+-----------------+-------------+------+-----+---------+----------------+

mysql> describe people_membership;
+---------------+--------+------+-----+---------+----------------+
| Field         | Type   | Null | Key | Default | Extra          |
+---------------+--------+------+-----+---------+----------------+
| id            | int(3) | NO   | PRI | NULL    | auto_increment |
| people_id     | int(3) | NO   |     |         |                |
| membership_id | int(2) | NO   |     |         |                |
+---------------+--------+------+-----+---------+----------------+
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

What is the SQL engine supposed to do with this:

Code: Select all

AND people.people_id=people_membership.people_id
        (SELECT membership.membership_name 
Look at it and read it to yourself. This is where the problem lies.
Post Reply