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 :slight_smile:

Any help is much appreciated!

Regards,
Greg

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.

Well,

I needed to move on with the site, so I used that redneck way of coding:


$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:

$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…


SELECT
    users.id
FROM companies
INNER JOIN users
ON companies.id = users.company_ID
WHERE companies.job_type_ID = '6'

Works like a charm!

full script that I used:

$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!
:slight_smile: