CODEWITHSUNDEEP
phpmysql
Kaff1n8t3d
Kaff1n8t3d

Reputation: 81

MySQL returning results from one table based on id data in another table

I tried to make this inside this question, but i am too young on #stackoverflow to post comments. MySQL returning results from one table based on data in another table

I cannot get this to work. My intentions are slightly different. I have two tables (and more in the future) that I intend to work together. I want to keep my db size down, so instead of using full words to reference time_code_department, I added a column to reference the "department_id". now I want to grab all the "time_codes" from table where the "time_code_depart" id matches the variable entered. So if user selects "Solar" department and time_code_department table has "9" as the "solar" "department_id", then i want to return all the entries in "time_codes" that have the "department_id" "9" on the time_codes table. Which in this example would be lines with id 40 and 75.

Table Structure:

 ----------------------------------------------
|                 time_codes  (table)          |
|                                              |
| id | department_id | code_number | code_name |
 ----------------------------------------------
| 40 | 9             | 35          | Safety    |
| 52 | 10            | 725         | Inventory |
| 75 | 9             | 18          | Cabinets  |
 ----------------------------------------------


 -----------------------------------
|      time_code_depart (table)     |
|                                   |
| department_id | name    | manager |
 ----------------------------------- 
| 9             | Solar   | John    |
| 10            | Finance | Mary    |
| 11            | Design  | Sue     |
 -----------------------------------

I've tried to query:

SELECT 'department_id' 
FROM `time_codes` 
INNER JOIN `time_code_depart` 
ON 'time_codes.department_id' = 'time_code_depart.department_id' 
WHERE 'name' LIKE 'Solar'

and

SELECT 'time_codes.id', 'time_codes.code_number', 'time_codes.code_name' 
FROM `time_codes` 
ON 'time_codes.department_id' = 'time_code_depart.department_id' 
WHERE 'time_code_depart.name' 
LIKE 'Solar'

Both of these I formed based on several readings on the subject, and i have used several variation of sentax. I cannot get it to return the entries for the lines with id 40 and 75. Can you help me identify where I am going wrong?

Upvotes: 3

Views: 5837

Answers (1)

Barmar
Barmar

Reputation: 780818

You have several problems with quoting.

First, to quote table or column names in MySQL, you use backticks; single quotes are used for making strings.

Second, when you have a table.column, you must quote them each separately.

Note that it normally isn't necessary to quote table and column names at all. They only need to be quoted if they're the same as reserved words, or contain punctuation characters.

SELECT `time_codes`.`department_id`
FROM `time_codes`
INNER JOIN `time_code_depart`
ON `time_codes`.`department_id` = `time_codes_depart`.`department_id`
WHERE `name` LIKE 'Solar'

And when you have long table names like this, I recommend making use of table aliases to make expressions more readable:

SELECT tc.department_id
FROM time_codes AS tc
INNER JOIN time_code_depart AS tcd
ON tc.department_id = tcd.department_id
WHERE name LIKE 'Solar'

Upvotes: 2

Related Questions