Page 1 of 1

Table Joining

Posted: Wed Feb 02, 2011 5:02 pm
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!

Re: Table Joining

Posted: Wed Feb 02, 2011 6:31 pm
by VladSun
You should/must not have a table per each project. You should have just one table - "project"... Fix that first

Re: Table Joining

Posted: Wed Feb 02, 2011 6:33 pm
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.

Re: Table Joining

Posted: Wed Feb 02, 2011 6:35 pm
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 :)