SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Addict greg76's Avatar
    Join Date
    Aug 2004
    Location
    Poland
    Posts
    273
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Understanding the JOINs

    Hiya all,

    I've been trying to write a query that selects one id from one table that is based on informations stored in two other tables.

    I have purchased Rudy's book 'Simply SQL', but rather than reading it page after page, I sort of jumped into a chapter with no previous understanding of everything else.

    I have these three tables:
    job type:
    id - name
    1 - handyman
    2 - cleaning
    3 - security

    companies
    id - company_name - job_type_ID
    1 - Cleaning Company Ltd - 2
    2 - My Security Firm - 3

    contractors
    id - name - company_ID
    1 - Bob - 2 ( My Security Firm -> security )
    2 - Greg - 1 ( Cleaning Company Ltd -> cleaning )

    Now, if someone enters a new job, and this is a security job, then it means that the My Security Firm should be selected as a company, and person named Bob as their man on the site. I need to grab that person's ID based on what job type has been selected.

    I could do that redneck style and write two loops and grab that bloody ID in almost no time but now it's the matter of honour to understand at least basics of the real coding

    Any help is much appreciated!

    Regards,
    Greg

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,501
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Well, you already know that you need to use joins, right?
    Did you try to write a query? Post it here, so we can see how far you've managed to come.

  3. #3
    SitePoint Addict greg76's Avatar
    Join Date
    Aug 2004
    Location
    Poland
    Posts
    273
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well,

    I needed to move on with the site, so I used that redneck way of coding:
    Code PHP:
    $q = $action->selectfromdb('id, company_name', 'companies', 'job_type_ID = \'6\'');
    $row = mysql_fetch_array($q);
     
    $q = $action->selectfromdb('id, name, surname', 'users', 'company_ID = \'' . $row['id'] . '\' && def = \'1\'');
    $row = mysql_fetch_array($q);

    which of course works.

    Now, I have been trying to use something I had seen once but then again, I don't remember exact syntax, so the query creates a syntax error:

    Code PHP:
    $sel = mysql_query("
    select id from users where company_ID = (select id from companies where job_type_ID = '6')
    ") or die (mysql_error());
     
    $row = mysql_fetch_array($sel);
    echo $row['id'];

    and after removing extra bracket that one works now, too. But no clue how to make the JOINs to work...

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,501
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Code:
    SELECT
        users.id
    FROM companies
    INNER JOIN users
    ON companies.id = users.company_ID
    WHERE companies.job_type_ID = '6'

  5. #5
    SitePoint Addict greg76's Avatar
    Join Date
    Aug 2004
    Location
    Poland
    Posts
    273
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Works like a charm!

    full script that I used:
    Code PHP:
    $sel = mysql_query("
    SELECT
        users.id
    FROM companies
    INNER JOIN users
    ON companies.id = users.company_ID
    WHERE companies.job_type_ID = '" . $_POST['job_type'] . "' && users.def = '1'
    ") or die (mysql_error());
    $row = mysql_fetch_array($sel);
    When I see that code it looks so simply...

    Grazie mille!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •