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