Become a MacRumors Supporter for $50/year with no ads, ability to filter front page stories, and private forums.

CavemanUK

macrumors 6502
Original poster
Jun 29, 2006
449
61
Rhyl, North Wales
Hi,

I'm trying to get to grips with MySQL Databases and Tables.. I was wondering if it was possible to do the following..

Say I have 2 tables... This is a random example so dont take the design to literally..

Jobs
-----
job_id
engineer_id
job_description
job_date


Engineers
-----------
engineer_id
engineer_name
engineer_phoneno


Ok,

Well i understand that if I run the query "SELECT * FROM Jobs" that I will get the list of jobs... but is it possible to return an array that includes the engineers details in it... so the array/table returned would have...


job_id
engineer_name
engineer_phoneno
job_description
job_date


I have done something similar in the past but it involved a loop and lots of queries.. this doesnt seem right..

hope you get what i mean
 

brn2ski00

macrumors 68020
Aug 16, 2007
2,239
12
MA
try something like this:

Code:
select Jobs.job_id, Engineers.engineer_name, Engineers.engineer_phoneno, Jobs.job_description, Jobs.job_date
where Engineers.job_id = 123 AND Jobs.job_id = 123

i think that you need to specify the job_id to get a list of Engineers with that job_id.

not sure if the syntax is correct, but that should work
 

weinrdog

macrumors newbie
Oct 25, 2007
24
0
o-HI-o
From the example, others are close, but since engineer doesn't include a job_id...

select job_id, engineer_name, engineer_phoneno, job_description, job_date
from jobs, engineers
where jobs.engineer_id = engineers.engineer_id

You shouldn't need to prefix each column since they are unique. If you want to specify a column name that exists in both tables, you'd need to prefix that column with the table name.
 

memco

macrumors 6502
May 1, 2008
259
2
Code:
SELECT job_id, engineer_name, engineer_phoneno, job_description, job_date
FROM jobs
JOIN engineers
ON jobs.engineer_id=engineers.engineer_id

Roughly the same as mentioned above, but you really should be using joins for these types of things.
 

ulbador

macrumors 68000
Feb 11, 2010
1,554
0
Code:
SELECT job_id, engineer_name, engineer_phoneno, job_description, job_date
FROM jobs
JOIN engineers
ON jobs.engineer_id=engineers.engineer_id

Roughly the same as mentioned above, but you really should be using joins for these types of things.

This is largely a matter of what database you use. Your example will accomplish the same as the example above. Not including the join keyword will do an implied join in most databases
 

splitpea

macrumors 65816
Oct 21, 2009
1,133
395
Among the starlings
The above examples will exclude any engineers without jobs or jobs without engineers.

To include jobs without engineers:
Code:
SELECT * FROM jobs j LEFT JOIN engineers e ON j.engineer_id=e.engineer_id

To include engineers without jobs:
Code:
SELECT * FROM engineers e LEFT JOIN jobs j ON e.engineer_id=j.engineer_id
 
Register on MacRumors! This sidebar will go away, and you'll see fewer ads.