mysql - How can I SQL Join against my user table in multiple columns? -


i have query populate datatable. database table has multiple columns list id user table. case_officer, created_by, modified_by , location_user_id. whenever deal 1 of columns, want return rank , last name of user user_account table instead of user_id. i'm getting lost in concat , join language. here have far:

select sql_calc_found_rows item.item_id      , item.item_code      , item.check_out_flag      , item.created_by      , item.creation_date      , concat(user_account.rank, " ", user_account.last_name) case_officer      , lookup_casetype.short_description case_type      , item.incident_number      , lookup_itemstatus.short_description item_status      , lookup_finaldisposition.short_description final_disposition      , lookup_danger.short_description danger_flag      , lookup_mso.short_description most_serious_offense      , item.item_description      , lookup_itemtype.short_description item_type      , lookup_drugpackaging.short_description drugs_packaging_type      , item.drugs_substance, item.drugs_weight      , item.make, item.model, item.caliber, item.serial, item.money_value      , item.year, item.registration      , lookup_state.short_description registration_state      , lookup_color.short_description color, item.name_1      , item.name_2, item.name_3, item.name_4      , lookup_nametype.short_description name_1_type      , lookup_nametype.short_description name_2_type      , lookup_nametype.short_description name_3_type      , lookup_nametype.short_description name_4_type      , item.note, item.recorder_data, item.recovery_info      , location.short_description location_id      , lookup_itemclass.short_description item_class_id      , concat(user_account.rank, " ", user_account.last_name) modified_by      , item.modified_date, location_user_id, item.deleted_flag, item.recovery_datetime item left join lookup_itemstatus on lookup_itemstatus.item_status_id = item.item_status left join lookup_casetype on lookup_casetype.item_type_id = item.case_type left join lookup_finaldisposition on lookup_finaldisposition.finaldisposition_id = item.final_disposition left join lookup_danger on lookup_danger.danger_id = item.danger_flag left join lookup_mso on lookup_mso.mso_id = item.most_serious_offense left join lookup_itemtype on lookup_itemtype.item_type_id = item.item_type left join lookup_drugpackaging on lookup_drugpackaging.drug_packaging_id = item.drugs_packaging_type left join lookup_state on lookup_state.state_id = item.registration_state left join lookup_color on lookup_color.color_id = item.color left join lookup_nametype on lookup_nametype.name_type_id = item.name_1_type , item.name_2_type , item.name_3_type , item.name_4_type left join location on location.location_id = item.location_id left join lookup_itemclass on lookup_itemclass.item_class_id = item.item_class_id left join user_account on (user_account.user_account_id = item.case_officer)  order item.item_code desc limit 0, 150

... works fine case_officer, i'm not sure how same thing modified_by, created_by , location_user_id without error.

any appreciated!

looks need second join user_account table. @ least 1 of references need table alias disambiguate references. i'd give alias both references... co case_officer reference , mb modified_by reference. follow same pattern other joins same table.

e.g.

select ...      , ...      , concat(co.rank, " ", co.last_name) case_officer      , ...      , concat(mb.rank, " ", mb.last_name) modified_by      , ...      , concat(cb.rank, " ", cb.last_name) created_by      , ...      , concat(lu.rank, " ", lu.last_name) location_user_id      , ...  ... left join user_account co on (co.user_account_id = item.case_officer) left join user_account mb on (mb.user_account_id = item.modified_by) left join user_account cb on (cb.user_account_id = item.created_by) left join user_account lu on (lu.user_account_id = item.location_user_id)

Comments

Post a Comment

Popular posts from this blog

javascript - Bootstrap Popover: iOS Safari strange behaviour -

i looking through twitter bootstrap documentation, , i'm interested in implementing dismissible popover when button clicked. here example bootstrap's docs: http://getbootstrap.com/javascript/#dismiss-on-next-click when press "dismissible popover" button os x safari browser works fine. when access same link above ios safari, button not work. is, popover not dismissed on click. why button not working ios safari? the example linked using data-trigger="focus" attribute. try changing data-trigger="click" . <a tabindex="0" class="btn btn-lg btn-danger" role="button" data-toggle="popover" data-trigger="click" title="dismissible popover" data-content="and here's amazing content. it's engaging. right?">dismissible popover</a>
Read more

Magento/PHP - Get phones on all members in a customer group -

i working on custom magento extension. working around adminhtml part , i've created custom form there. here form code: <?php class vivasindustries_smsnotification_block_adminhtml_sms_sendmass_edit_form extends mage_adminhtml_block_widget_form { public function _preparelayout() { $extensionpath = mage::getmoduledir('js', 'vivasindustries_smsnotification'); $head = $this->getlayout()->getblock('head'); $head->addjs('jquery.js'); $head->addjs('vivas.js'); return parent::_preparelayout(); } protected function _prepareform() { $form = new varien_data_form(array( 'id' => 'edit_form', 'action' => $this->geturl('*/*/save', array('id' => $this->getrequest()->get...
Read more

session - Logging Out Using PHP -

i have seen other programs regarding can't seem make work. doing wrong? want achieve after user logged out, going previous pages become restricted. i have 3 php files: 1. sample.php - main page username , password filled in 2. successful.php - user's page after logging in 3. logout.php - destorys session hinder user access pages after logging out sample.php </html> <form action="" method="post"> <table> <tr> <td>username:</td> <td>password:</td> </tr> <tr> <td><input type="text" name="username"></td> <td><input type="password" name="password"></td> </tr> <tr> <td><input type="submit" value="submit" name="submit"></td> </tr> </table> </form> </html> <?php $servername = "localhost"; $username = ...
Read more