MySQL Query question

Discussion in 'Web Design and Development' started by CavemanUK, Apr 26, 2010.

  1. CavemanUK macrumors 6502

    CavemanUK

    Joined:
    Jun 29, 2006
    Location:
    Rhyl, North Wales
    #1
    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
     
  2. brn2ski00 macrumors 68020

    brn2ski00

    Joined:
    Aug 16, 2007
    #2
    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
     
  3. Authentic AB macrumors newbie

    Joined:
    May 28, 2009
    Location:
    Ottawa Canada
    #3
    A little simpler:

    select * from jobs, engineers where
    jobs.job_id = engineers.job_id;

    No loops and 1 query.
     
  4. weinrdog macrumors newbie

    Joined:
    Oct 25, 2007
    Location:
    o-HI-o
    #4
    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.
     
  5. memco macrumors 6502

    Joined:
    May 1, 2008
    #5
    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.
     
  6. ulbador macrumors 68000

    ulbador

    Joined:
    Feb 11, 2010
    #6
    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
     
  7. splitpea macrumors 6502a

    Joined:
    Oct 21, 2009
    Location:
    Among the starlings
    #7
    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
     

Share This Page