database design - MYSQL populate foreign id with value -

database design - MYSQL populate foreign id with value -

i have printed table here, , issue query effort bring together tables tech_id, clients_id, job_id, part_id should populate corresponding key in tables / column too.

here query:

select * work_orders, technicians tech, parts_list parts, job_types job, clients client left bring together technicians on tech_id = technicians.tech_name left bring together parts_list on part_id = parts_list.part_name left bring together job_types on job_id = job_types.job_name left bring together clients on clients_id = clients.client_name

i've messed around multiple different variations, 1 seem syntax correct, i'm getting: column 'clients_id' in on clause ambiguous

i'm sure happen not clients maybe others. want able print table in image above, clients listed. possible done via 1 query well? thanks.

you have 2 problems.

first (this might not problem, that's "good practice"), shouldn't utilize select *, indeed have field same name in different tables.

this 1 (of many) reason avoid * in select clause.

then, main problem select tables in clause, , 1 time again joining.

problematic line :

from work_orders, technicians tech, parts_list parts, job_types job, clients client

so (i don't know table structure, may errors, you've got idea)

select w.client_id, t.tech_name --etc work_orders w left bring together technicians t on c.tech_id = t.tech_name left bring together parts_list p on c.part_id = p.part_name left bring together job_types j on w.job_id = j.job_name left bring together clients c on w.clients_id = c.client_name

mysql database-design