[SOLVED] multiple references in a table?

[SirDuke]

Hi there. This is my 1st post in this (surprisingly good-looking) forum.

 

i am using mySQL for a project

 

i have a database to manage a company and have a table for emploees (name, salary, ...) and a table for jobs (title, cost, ...). Each emploee can work in many jobs and of course each job is done by many emploees. How do i implement this in mySQL??

 

 

I cannot add a field "emploee_id" in the jobs table cause clearly there are more than 1 emploees on the job. How can i make this dynamic? Always have space for more emploees and never unused space?

 

I am sure this a trivial matter cause it is very common, and i would love to learn the solution 

[teng84]

you will have a crossing table or a normalization something like

 

employee

---empid

---name

--ect....

 

job

--jobid

--jobdesc

--etc..

 

eployeeXjob

--empid

--jobid

 

Select * form employee

left join job on  eployee.empid = job.empid

left join employeeXjob on employeeXjob.empid = employee.empid

etc....

[SirDuke]

hmm.. interesting answer..

 

unfortunately i have no idea what "left join" does so i'll have to look it up (if there is a really good resource somewhere feel free to let me know -apart from the mysql docs)

 

Thanx for  the answer, though i might return tomorrow with more questions

[teng84]

http://w3schools.com/sql/sql_join.asp