mysql - Conditional LEFT INNER JOIN query with a conditional SELECT on the right table -

mysql - Conditional LEFT INNER JOIN query with a conditional SELECT on the right table -

before type this, have tried , tried find solution other threads here , searching google. cant seem answer. consider myself pretty sql cant seem grips problem @ all.

i have 2 tables:

i trying array of ref_departments.id , ref_departments.department_name, used dropdown on frontend populate company_department_norm table. should best pointed out @ point have no technical issues in creating dropdown etc - beingness built in yii framework , actual dropdown in place. problem identifying right sql statement bring on info want populating dropdown (i using yii's chtml helper class).

the first status of select statement looking create 1 query on ref_departments table select rows values in id (pk) column not found in company_department_norm.department_id column. done valid options available in list. have query working, , looks this...

select rd.id, rd.department_name ref_departments rd left outer bring together company_department_norm cdn on (cdn.department_id = rd.id) (cdn.department_id null)

additional integrity/data validation done on end (in yii model class).

the next step of part totally stuck on. within yii model company_department_norm, company_id field states fk company (as can see above). passed parameter query add together additional status query join statement match rows on right table (company_department_name) company_id same company_id passed model. testing purposes, trying working company_id of integer 1 hard-coded sql statement. however, life of me, cant seem work out how this.

i have attempted modify above query using this: -

left outer bring together (select * company_department_norm company_department_norm.company_id = 1) cdn

however, doesn't seem filter right table @ all.

can help me this? imagine more experienced sql users able identify solution quite have been pulling hair out hours trying solve this!!!

you can set company_id status in on clause:

select rd.id, rd.department_name ref_departments rd left outer bring together company_department_norm cdn on (cdn.department_id = rd.id , cdn.company_id = 1) (cdn.department_id null)

this returns section isn't represented yet @ company 1, versus first query returns section isn't represented yet @ any company.

re comment: given illustration data, query won't show difference between using company_id status or not, because both departments are represented @ company 1.

i have tested on mysql 5.5 , works fine. here's illustration more info demonstrates better:

use test; drop table if exists ref_departments; create table ref_departments ( id int primary key, department_name varchar(20) ); insert ref_departments values (1, 'accounts'), (2, 'hr'), (3, 'it'); drop table if exists company_department_norm; create table company_department_norm ( id int primary key, company_id int, department_name varchar(20), department_id int ); insert company_department_norm values (1, 1, 'accounting', 1), (2, 1, 'hr', 2), (3, 2, 'accounts', null), (4, 2, 'hr', null), (5, 2, 'it', 3);

now query depts not in company 1, , correctly shows "it":

select rd.id, rd.department_name ref_departments rd left outer bring together company_department_norm cdn on (cdn.department_id = rd.id , cdn.company_id = 1) (cdn.department_id null); +----+-----------------+ | id | department_name | +----+-----------------+ | 3 | | +----+-----------------+

now query depts not in company 2. though company 2 names accounts , hr, doesn't have numeric department_id on bring together status based. thinks 2 depts not matched.

select rd.id, rd.department_name ref_departments rd left outer bring together company_department_norm cdn on (cdn.department_id = rd.id , cdn.company_id = 2) (cdn.department_id null); +----+-----------------+ | id | department_name | +----+-----------------+ | 1 | accounts | | 2 | hr | +----+-----------------+

if you're getting other result, either have not used query described, or info not described.

mysql sql table join conditional