Table Joining

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
jamgood96
Forum Newbie
Posts: 12
Joined: Fri Jan 21, 2011 12:19 am

Table Joining

Post by jamgood96 »

I'm trying to get my head wrapped around table joining. What I have so far is as follows:


[text]<customer_accounts>
customer_id first_name last_name

<employee_accounts>
employee_id username password

<project1>
project1_id customer_id employee_id

<project2>
project2_id customer_id employee_id[/text]


What I want to do is query the database to show me the first and last name of the customer who has a project with employee X. So maybe one customer works with employee 1 on project1, but not project2. Maybe another customer works with employee 1 on project2, but not project1. So on and so forth...

I've tried the following, but

Code: Select all

SELECT customer_accounts.first_name,customer_accounts.last_name
FROM customer_accounts,project1,project2
WHERE project1.employee_id=1 AND project2.employee_id=1 AND customer_accounts.customer_id=project1.customer_id
I'm feeling I'm way off target on my attempt, so please go easy on me :-)

Thanks in advance!
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Table Joining

Post by VladSun »

You should/must not have a table per each project. You should have just one table - "project"... Fix that first
There are 10 types of people in this world, those who understand binary and those who don't
jamgood96
Forum Newbie
Posts: 12
Joined: Fri Jan 21, 2011 12:19 am

Re: Table Joining

Post by jamgood96 »

VladSun wrote:You should (must) not have a tbel per each project. You should have just one table - "project"... Fix that first
The problem is the projects are all extremely different from one another, each with completely different information. One table is not an option for this, and if it were, it would have 300 columns.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Table Joining

Post by VladSun »

The problem is the projects are all extremely different from one another, each with completely different information. One table is not an option for this, and if it were, it would have 300 columns.
Take a look into the EAV approach - http://en.wikipedia.org/wiki/Entity-att ... alue_model
One table :)
There are 10 types of people in this world, those who understand binary and those who don't
Post Reply